CODEWITHSUNDEEP
sql-serverxml
DataDev
DataDev

Reputation: 91

nested element FOR XML in SQL Server

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?

Upvotes: 3

Views: 4863

Answers (1)

DataDev
DataDev

Reputation: 91

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>

Upvotes: 4

Related Questions