Search code examples
sql-serverxmlsql-server-2012for-xml-path

Sql - FOR XML Path query issue


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?


Solution

  • 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>