need help to shred xml data - 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

SQL Server 2005 Forums

Transact-SQL (2005)

need help to shred xml data

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2010-02-27 : 03:00:31

i'm beginner using xml as input
i've statement as follow,

declare @coutshort xml
set @coutshort='<coutshortcuts>
<coutshortcut>
<cout>kl</cout>
<shortcuts> 
<shortcut>kntn</shortcut>
<shortcut>jb</shortcut>
</shortcuts>
</coutshortcut>
</coutshortcuts>
'

how to shred the xml data, and display as follow,
cout | shortcut
-----------------------
kl | kntn
kl | jb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 03:25:29

[code]
declare @coutshort xml
set @coutshort='<coutshortcuts>
<coutshortcut>
<cout>kl</cout>
<shortcuts>
<shortcut>kntn</shortcut>
<shortcut>jb</shortcut>
</shortcuts>
</coutshortcut>
</coutshortcuts>
'

select a.b.value('cout[1]','varchar(100)') as cout,
t.u.value('.','varchar(100)') as shortcut
from @coutshort.nodes('/coutshortcuts/coutshortcut') a(b)
cross apply b.nodes('shortcuts/shortcut')t(u)

output
--------------------------
cout shortcut
kl kntn
kl jb

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/