I have the following query
Select field1 as 'node1/field1',
field2 as 'node1/field2',
(Select field3 as 'child1/field3',
field4 as 'child1/field4'
From table2
FOR XML PATH(''),TYPE,Elements)
From Table1 FOR XML PATH('Root'),Elements
This produces:
<Root>
<node1>
<field1>data1</field1>
<field2>data2</field2>
</node1>
<child1>
<field3>data3</field3>
<field4>data4</field4>
</child1>
<child1>
...
</Root>
I would like the child1 nodes to be part of node1, not a separate node below.
<Root>
<node1>
<field1>data1</field1>
<field2>data2</field2>
<child1>
<field3>data3</field3>
<field4>data4</field4>
</child1>
<child1>
...
</node1>
<node1>
...
</Root>
I've tried putting node1 in the subquery PATH
FOR XML PATH('node1'),TYPE,Elements)
or prefixing the subquery field names with node1
Select field3 as 'node1/child1/field3',
but both create a new node1 element for the subquery.
Does anyone know how I can accomplish this?
Thanks