how to loop inside an xml input_XMLShredding

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

how to loop inside an xml input_XMLShredding

Author

Topic

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-02 : 01:38:14

HI ALL,
I have an Input xml like this
<USER>
<NAME NAMEID='1023'NAME='JOE'ADDRESS='CITY1'>
<BOOKS NAME='BOOK1'DEATILS='FJKJIII' TYPE='0'>
<BOOKS NAME='BOOK1'DEATILS='FJKJIII' TYPE='1'>
<BOOKS NAME='BOOK1'DEATILS='FJKJIII' TYPE='0'>
<BOOKS NAME='BOOK1'DEATILS='FJKJIII' TYPE='1'>
</NAME>
<NAME NAMEID='1001'NAME='ANDREW'ADDRESS='CITY2'>
<BOOKS NAME='BOOK1'DEATILS='FJKJIII' TYPE='0'>
<BOOKS NAME='BOOK1'DEATILS='FJKJIII' TYPE='1'>
<BOOKS NAME='BOOK1'DEATILS='FJKJIII' TYPE='0'>
<BOOKS NAME='BOOK1'DEATILS='FJKJIII' TYPE='1'>
</NAME>
</USER>

if the type='0' i have into the table books if its type='1' then updation has to be done. How i can iterate through inner node and check the condition inside input xml. I am using SQL server 2008

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 04:03:24

make use of nodes() function

http://msdn.microsoft.com/en-us/library/ms188282.aspx

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-02 : 04:28:35

thank you visakh......

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:03:36

welcome

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-02 : 05:16:32

i have done like this but i am getting error

DECLARE @data XML
SET @Data = '
<POInfo>
<PO
<POID="100"RouteID="P1"CatalogID="J001"RequestedQty="100"Priority="1"UserID="Administrator"/>
<POTags>
<Tag ID="Test1" value="10">
<Tag ID="Test2"value="20"/>
<Tag ID="combo1"value="aaa"/>
<Tag ID="combo2"value="p1"/>
<Tag ID="checkTest"value="false"/>
<Tag ID="intTest"value="11"/>
<Tag ID="realTest"value="50.5"/>
</POTags>
</PO>
<PO>
<POID="200"RouteID="P2"CatalogID="F003"RequestedQty="200"Priority="2"UserID="Administrator"/>
<POTags>
<Tag ID="Test1" value="10"/>
<Tag ID="Test2"value="20"/>
<Tag ID="combo1"value="aaa"/>
<Tag ID="combo2"value="p1"/>
<Tag ID="checkTest"value="false"/>
<Tag ID="intTest"value="11"/>
<Tag ID="realTest"value="50.5"/>
</POTags>

</PO>
</POInfo>
'

SELECT
p.n.value('POID[1]', 'INT') AS POID,
p.n.value('RouteID[1]', 'VARCHAR(10)') AS RouteID,
p.n.value('CatalogID[1]', 'VARCHAR(6)') AS CatalogID,
t.n.value('@ID', 'VARCHAR(200)') AS TagID,
t.n.value('.', 'VARCHAR(200)') AS TagValue
FROM @data.nodes('POInfo/PO') AS p(n)
CROSS APPLY n.nodes('POTags/Tag') AS t(n)

error is

Msg 9455, Level 16, State 1, Line 15
XML parsing: line 4, character 3, illegal qualified name character

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:18:58

souldnt last line be

t.n.value('@value', 'VARCHAR(200)') AS TagValue

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-02 : 05:26:15

ya i got it....:)

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:26:45

good

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-02 : 05:48:44

One more problem valur is not getting fetched to POID,RouteID,CatalogID

POID RouteID CatalogID TAg id values
NULL NULL NULL Test1 10
NULL NULL NULL Test2 20
NULL NULL NULL combo1 aaa
NULL NULL NULL combo2 p1
getting O/P like this

i made changes like this
<PO>
<pog POID="100" RouteID="P1" CatalogID="J001" RequestedQty="100" Priority="1" UserID="Administrator" />

and

FROM @data.nodes('POInfo/PO/pog') AS p(n)

but that there is no O/P

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 05:52:31

then they should be

p.n.value('@POID', 'INT') AS POID,
p.n.value('@RouteID', 'VARCHAR(10)') AS RouteID,
p.n.value('@CatalogID', 'VARCHAR(6)') AS CatalogID

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-02 : 06:06:34

i have tried that also but no output is coming :(

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 06:11:41

quote:
Originally posted by divyaram

i have tried that also but no output is coming :(

Regards,
Divya

make sure you're using them in correct case ( as xml node labels are case sensitive). also try

p.n.value('./@POID', 'INT') AS POID,
p.n.value('./@RouteID', 'VARCHAR(10)') AS RouteID,
p.n.value('./@CatalogID', 'VARCHAR(6)') AS CatalogID

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-02 : 06:24:42

i have made some changes in xml input now its working fine....
<POInfo>
<PO POID="100" RouteID="P1" CatalogID="J001" RequestedQty="100" Priority="1" UserID="Administrator">
<POTags>
<Tag ID="Test1" value="10" />
<Tag ID="Test2" value="20"/>
and in select

SELECT
p.n.value('@POID', 'INT') AS POID,
p.n.value('@RouteID', 'VARCHAR(10)') AS RouteID,
p.n.value('@CatalogID', 'VARCHAR(6)') AS CatalogID,

Regards,
Divya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-02 : 06:25:49

great

divyaram
Posting Yak Master

180 Posts

Posted - 2010-02-02 : 06:30:29

Regards,
Divya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources