I have query written as below (sample).
Select 'Somthing' as Title,
'Some notes' as Notes,
(Select Path1
From (Select 'One' Path1
union
Select 'Two' Path1
union
Select 'Three' Path1) T
FOR XML PATH('Image'),ROOT('Images'), ELEMENTS, TYPE),
'Other value' as Value
FOR XML PATH('ItemRow'),TYPE,ELEMENTS
Which outputs below xml
<ItemRow>
<Title>Somthing</Title>
<Notes>Some notes</Notes>
<Images>
<Image>
<Path1>One</Path1>
</Image>
<Image>
<Path1>Two</Path1>
</Image>
<Image>
<Path1>Three</Path1>
</Image>
</Images>
<Value>Other value</Value>
</ItemRow>
I am trying to put Notes and Images in to parent node, so it should appear as below
<ItemRow>
<Title>Somthing</Title>
<SomeParentNode>
<Notes>Some notes</Notes>
<Images>
<Image>
<Path1>One</Path1>
</Image>
<Image>
<Path1>Two</Path1>
</Image>
<Image>
<Path1>Three</Path1>
</Image>
</Images>
</SomeParentNode>
<Value>Other value</Value>
</ItemRow>
It this possible?
Just add SomeParentNode
like this:
Select 'Somthing' as Title,
'Some notes' as 'SomeParentNode/Notes', -- here
(Select Path1
From (Select 'One' Path1
union
Select 'Two' Path1
union
Select 'Three' Path1) T
FOR XML PATH('Image'),ROOT('Images'), ELEMENTS, TYPE) AS 'SomeParentNode', -- and here
'Other value' as [Value]
FOR XML PATH('ItemRow'),TYPE,ELEMENTS
Output:
<ItemRow>
<Title>Somthing</Title>
<SomeParentNode>
<Notes>Some notes</Notes>
<Images>
<Image>
<Path1>One</Path1>
</Image>
<Image>
<Path1>Two</Path1>
</Image>
<Image>
<Path1>Three</Path1>
</Image>
</Images>
</SomeParentNode>
<Value>Other value</Value>
</ItemRow>