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.
| Author |
Topic |
|
Glockenspiel
Yak Posting Veteran
50 Posts |
 Posted - 2014-03-30 : 14:52:35
|
Hi,
I'm new to using XML in SQL Server and need to retrieve data from an XML document formatted like this:
<data>
<Config>
<Level>Foo</Level>
<BogusFields>
<Field>Rikki</Field>
<Field>Tikki</Field>
<Field>Tavi</Field>
</BogusFields>
</Config>
<Config>
<Level>Boo</Level>
<BogusFields>
<Field>Luke</Field>
<Field>Han</Field>
<Field>Chewie</Field>
</BogusFields>
</Config>
</data>
And receive results like this:
Level Field
----- -----
Foo Rikki
Foo Tikki
Foo Tavi
Boo Luke
Boo Han
Boo Chewie
Can anyone point me to some resources for how this might be done? I imagine it's doable (I just haven't been able to figure it out)
Thanks,
--G |
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2014-03-31 : 01:28:12
|
[code]declare @xmlVar as xml
set @xmlVar='<data>
<Config>
<Level>Foo</Level>
<BogusFields>
<Field>Rikki</Field>
<Field>Tikki</Field>
<Field>Tavi</Field>
</BogusFields>
</Config>
<Config>
<Level>Boo</Level>
<BogusFields>
<Field>Luke</Field>
<Field>Han</Field>
<Field>Chewie</Field>
</BogusFields>
</Config>
</data>'
select
t.u.value('Level[1]','varchar(50)') as [Level]
,v.x.value('.','varchar(50)') as [Field]
from @xmlVar.nodes('/data/Config') t(u)
outer apply t.u.nodes('BogusFields/Field') v(x)
[/code]
and the output:
[code]
Level Field
Foo Rikki
Foo Tikki
Foo Tavi
Boo Luke
Boo Han
Boo Chewie
[/code]
sabinWeb MCP |
 |
|
|
Glockenspiel
Yak Posting Veteran
50 Posts |
Posted - 2014-03-31 : 02:04:21
|
| 1000 kudos and mega-thanks, stepson! The examples I tried were using CROSS APPLY but apparently OUTER APPLY is what I needed. Your solution was spot-on perfection. :) |
|
|
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2014-03-31 : 02:07:12
|
Glad to help you!
sabinWeb MCP |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-04-07 : 06:47:48
|
[code]SELECT n.value('(../../Level[1])', 'varchar(100)') AS [Level],
n.value('(.)', 'varchar(100)') AS [Field]
FROM @xmlVar.nodes('(/data/Config/BogusFields/Field)') AS x(n);[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
XML parsing with T-SQL