CODEWITHSUNDEEP
sql-serverxmlsql-server-2012for-xml-path
huMpty duMpty
huMpty duMpty

Reputation: 14470

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?

Upvotes: 2

Views: 415

Answers (2)

TT.
TT.

Reputation: 16144

  1. The long way, using subqueries to place the element in the proper path:
SELECT
    'Something' AS Title,
    (
        SELECT
            'Some Notes' AS Notes,
            (
                SELECT
                    Path1
                FROM    
                    (VALUES('One'),('Two'),('Three')) AS T(Path1)
                FOR 
                    XML PATH('Image'), TYPE
            )
        FOR 
            XML PATH('Images'), TYPE
    ),
    'Other value' as Value
FOR
    XML PATH('ItemRow')
  1. The short way, in which you select the element in the proper place by adding the root element name in the field name
SELECT
    'Something' AS Title,
    'Some Notes' AS [Images/Notes],
    (
        SELECT
            Path1
        FROM    
            (VALUES('One'),('Two'),('Three')) AS T(Path1)
        FOR 
            XML PATH('Image'), TYPE
    ) AS [Images],
    'Other value' as Value
FOR
    XML PATH('ItemRow')

Both result in:

<ItemRow>
  <Title>Something</Title>
  <Images>
    <Notes>Some Notes</Notes>
    <Image>
      <Path1>One</Path1>
    </Image>
    <Image>
      <Path1>Two</Path1>
    </Image>
    <Image>
      <Path1>Three</Path1>
    </Image>
  </Images>
  <Value>Other value</Value>
</ItemRow>

Upvotes: 1

gofr1
gofr1

Reputation: 15997

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>

Upvotes: 2

Related Questions