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 2012 Forums
 Transact-SQL (2012)
 XML parsing with T-SQL

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
Go to Top of Page

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. :)
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-03-31 : 02:07:12
Glad to help you!


sabinWeb MCP
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -