IvanH
IvanH

Reputation: 5139

How to get XML subnodes as strings along with parent attributes?

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

Answers (2)

Alexander Volok
Alexander Volok

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

John Cappelletti
John Cappelletti

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

enter image description here

Upvotes: 1

Related Questions