January 5, 2014 at 10:19 pm
Hi all,
I have one xml file and i want to convert this file into sql table.
Please any one help me on this.
<?xml version="1.0"?>
<UEI2Server xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Document Type="ModelsInput" Version="1.0.0" />
<Models>
<RemoteModel Value = "DHJG03">
<TargetModel Value = "KFR35GWA98"/>
<TargetModel Value = "N3KF25GW"/>
<TargetModel Value = "KFR32GW"/>
</RemoteModel>
<RemoteModel Value = "ZHJT03">
<TargetModel Value = "KFR35GWA98"/>
<TargetModel Value = "N3KF25GW"/>
<TargetModel Value = "KFR32GW"/>
</RemoteModel>
</Models>
</UEI2Server>
The xml data should be parsed and populated to table as mentioned below:
Remote ModelTarget Model
DHJG03KFR35GWA98
DHJG03N3KF25GW
DHJG03KFR32GW
ZHJT03 KFR35GWA98
ZHJT03 N3KF25GW
ZHJT03 KFR32GW
Thank you
January 5, 2014 at 11:54 pm
Hooray for attribute-centric XML! Makes stuff way easier.
The main trick here is to turn all of the <TargetModel> nodes into a table so we can go after each of the Value attributes as a resultset.
That's what's going on in the FROM clause: shredding an XML document into a SQL Server resultset so we can look at it on our terms.
Each <TargetModel /> node doesn't explicitly contain the value for the RemoteModel, but it's parent <RemoteModel /> node does, even though it appears stripped due to the .nodes() method. Crawling up document tree is easy, adding ../ to the XPath moves up one more level.
declare @x xml = '<?xml version="1.0"?>
<UEI2Server xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<Document Type="ModelsInput" Version="1.0.0" />
<Models>
<RemoteModel Value = "DHJG03">
<TargetModel Value = "KFR35GWA98"/>
<TargetModel Value = "N3KF25GW"/>
<TargetModel Value = "KFR32GW"/>
</RemoteModel>
<RemoteModel Value = "ZHJT03">
<TargetModel Value = "KFR35GWA98"/>
<TargetModel Value = "N3KF25GW"/>
<TargetModel Value = "KFR32GW"/>
</RemoteModel>
</Models>
</UEI2Server>';
SELECT RMs.TMs.value('./../@Value', 'varchar(20)') AS RemoteModel,
RMs.TMs.value('./@Value', 'varchar(20)') AS TargetModel
FROM @x.nodes('/UEI2Server/Models/RemoteModel/TargetModel') AS RMs(TMs);
-Eddie
Eddie Wuerch
MCM: SQL
January 5, 2014 at 11:58 pm
Thank you so much... thanks a lot :-):-):-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply