Data from XML to Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data from XML to Table

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2014-09-23 : 08:13:54

hi Team,

I tired to insert data from xml to a table .but only null value is getting inserted into the table.The query is attached below.

create table Detail_test1
(
DetailId Int,
LaId Int,
OcCode Varchar,
BId INT,
Rate INT)

Declare @xml XML
set @xml='<ArrayOfBDetailLine>
<BDetailLine DetailId="0" LaId="0" OcCode="" BId="332080" Rate="0" />
<BDetailLine DetailId="0" LaId="97426" OcCode="89389" BId="332080" Rate="34691" />
<BDetailLine DetailId="0" LaId="97426" OcCode="89389" BId="332080" Rate="34691" />
<BDetailLine DetailId="0" LaId="0" OcCode="" BId="332080" Rate="850"/>
<BDetailLine DetailId="0" LaId="0" OcCode="" BId="332080" Rate="300" />
<BDetailLine DetailId="0" LaId="0" OcCode="" BId="332080" Rate="850" />
<BDetailLine DetailId="0" LaId="0" OcCode="" BId="332080" Rate="300" />
</ArrayOfBDetailLine>'

INSERT INTO Detail_test1
SELECT t.c.value( 'DetailId[1]','int')as 'LDetailId',
t.c.value( 'LaId[1]','int')as 'LId',
t.c.value( 'OcCode[1]','Varchar(50)')as 'OcCode',
t.c.value( 'BId[1]','int')as 'BId',
t.c.value( 'Rate[1]','int')as 'Rate'
FROM @xml.nodes('/ArrayOfBDetailLine/BDetailLine') T(c)

Regards,
Divya

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-23 : 08:31:43

The values you are trying to extract are ATTRIBUTES of the nodes, so you should prefix the names with an @ sign as in:

SELECT t.c.value( '@DetailId[1]','int')as 'LDetailId',
t.c.value( '@LaId[1]','int')as 'LId',
t.c.value( '@OcCode[1]','Varchar(50)')as 'OcCode',
t.c.value( '@BId[1]','int')as 'BId',
t.c.value( '@Rate[1]','int')as 'Rate' 
FROM @xml.nodes('/ArrayOfBDetailLine/BDetailLine') T(c)

Also, since there can be only one attribute with a given name under one node, you don't need the array index [1], although leaving it there does not do any harm.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources