XML parsing with T-SQL - 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 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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources