Reputation: 5139
I need to parse xml which consist of nodes having attributes and subnodes. The result should be attribute of parent with xml of child node
declare @xml xml
set @xml = '<root>
<group Description="firstgroup">
<nodeA age="10" birthplace="Anchorage"/>
<nodeB mode="A" ability="read"/>
</group>
<group Description="nextgroup">
<nodeA age="10" birthplace="London"/>
<nodeB count="2" birthplace="Paris"/>
</group>
</root>'
select
c.value('@Description', 'varchar(max)') as 'Description'
from @xml.nodes('/root/*') as T(c)
The output is
Description
===========
firstgroup
nextgroup
But I need
Description nodeBXML
=========== ========
firstgroup <nodeB mode="A" ability="read"/>
nextgroup <nodeB count="2" birthplace="Paris"/>
Upvotes: 2
Views: 145
Reputation: 5940
select
c.value('@Description', 'varchar(max)') as 'Description'
, c.query('./nodeB') as Content
from @xml.nodes('/root/*') as T(c)
-- Results to:
Description Content
firstgroup <nodeB mode="A" ability="read" />
nextgroup <nodeB count="2" birthplace="Paris" />
Upvotes: 2
Reputation: 81970
Perhaps something like this:
Example
Select c.value('@Description', 'varchar(max)') as 'Description'
,AsString = convert(varchar(max),c.query('./*[2]') )
,AsXML = c.query('./*[2]')
From @xml.nodes('/root/*') as T(c)
Returns
Upvotes: 1