nested element FOR XML in SQL Server

Question

I'm trying to create XML from SQL Server and I'm stuck with nested elements. I try different FOR XML parameters but still cannot get the correct results.

query looks like this:

SELECT 
Product.ID, Product.ProductName,
    (SELECT
    Images.ProductImage AS image
    FROM Images 
    WHERE Images.ProductID = Product.ID
    FOR XML PATH ('image_list'), ELEMENTS, TYPE
    )
FROM (SELECT DISTINCT ID, ProductName FROM Product) Product
FOR XML PATH ('products'), ELEMENTS, root ('Root')

and I want get this XML like this:

<Root>
  <products>
    <ID>1</ID>
    <ProductName>product 1</ProductName>
    <image_list>
      <image>picture1.jpg</image>
      <image>picture2.jpg</image>
      <image>picture3.jpg</image>
    </image_list>
  </products>
  <products>
    <ID>2</ID>
    <ProductName>product 2</ProductName>
    <image_list>
      <image>picture1.jpg</image>
      <image>picture2.jpg</image>
    </image_list>
  </products>
</Root>

First part is OK, the image_list is a problem. Any advice?

Hogan · Accepted Answer · 2017-01-24 19:20:30Z

4

I solved :) ..probably is good to post a qustion, after that brain starts to work :)

I add AS 'image_list' under subquery, remove ELEMENTS and PATH name. And that's it.

SELECT 
Product.ID, Product.ProductName,
    (SELECT
    Images.ProductImage AS image
    FROM Images 
    WHERE Images.ProductID = Product.ID
    FOR XML PATH (''), TYPE
    ) 'image_list'
FROM (SELECT DISTINCT ID, ProductName FROM Product) Product
FOR XML PATH ('products'), ELEMENTS, root ('Root')

Results is:

<Root>
  <products>
    <ID>1</ID>
    <ProductName>product 1 </ProductName>
    <image_list>
      <image>picture1.jpg</image>
      <image>picture2.jpg</image>
      <image>picture3.jpg</image>
    </image_list>
  </products>
  <products>
    <ID>2</ID>
    <ProductName>product 2 </ProductName>
    <image_list>
      <image>picture1.jpg</image>
      <image>picture2.jpg</image>
    </image_list>
  </products>
</Root>

edited Jan 24, 2017 at 19:20

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

answered Jan 24, 2017 at 17:58

DataDev

911 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hogan Over a year ago

For everyone's enjoyment add some example output

Collectives™ on Stack Overflow

nested element FOR XML in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related