CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
Imran Qadir Baksh - Baloch
Imran Qadir Baksh - Baloch

Reputation: 33954

How to put single row of result into a single element of xml?

I have the following SQL,

SELECT AT.ID ATT_ID
      ,VAL.ID VAL_ID
      ,COALESCE(ATT.Name,AT.Name) AS Name
      ,COALESCE(VALT.Value,VAL.Value) AS Value
      ,COALESCE(GT.Name,G.Name) AS GroupName
      ,AT.KeyAttribute
      ,AT.[Order]
FROM    Attributes AT
  LEFT OUTER JOIN AttributesTranslations ATT on (AT.ID = ATT.AttributeID AND ATT.LanguageID = @LanguageID)
  LEFT OUTER JOIN AttributesValues VAL ON VAL.AttributeID = AT.ID AND VAL.ProductID = @ProductID
  LEFT OUTER JOIN AttributesValuesTranslations VALT on (VAL.ID = VALT.AttributeValueID AND VALT.LanguageID = @LanguageID)
  LEFT OUTER JOIN AttributesGroups G ON G.ID = AT.GroupID
  LEFT OUTER JOIN AttributesGroupsTranslations GT on (G.ID = GT.AttributeGroupID AND GT.LanguageID = @LanguageID)
WHERE   AT.ProductTypeID = (SELECT ProductTypeID FROM Products WHERE ID = @ProductID)
    AND ((AT.RetailerID = @RetailerID) OR (@RetailerID = -1))
ORDER BY G.[order],G.Name ,AT.[Order]
FOR XML AUTO, ELEMENTS

It will return,

<AT>
  <ATT_ID>2</ATT_ID>
  <KeyAttribute>0</KeyAttribute>
  <Order>2</Order>
  <VAL>
    <VAL_ID>32614</VAL_ID>
    <Name>Design</Name>
    <Value>asfdsa</Value>
    <GroupName>zOther</GroupName>
  </VAL>
</AT>
<AT>
  <ATT_ID>2</ATT_ID>
  <KeyAttribute>0</KeyAttribute>
  <Order>2</Order>
  <VAL>
    <VAL_ID>32614</VAL_ID>
    <Name>Design</Name>
    <Value>asfdsa</Value>
    <GroupName>zOther</GroupName>
  </VAL>
</AT>

I want the result as,

<AT>
  <ATT_ID>2</ATT_ID>
  <KeyAttribute>0</KeyAttribute>
  <Order>2</Order>
    <VAL_ID>32614</VAL_ID>
    <Name>Design</Name>
    <Value>asfdsa</Value>
    <GroupName>zOther</GroupName>
</AT>
<AT>
  <ATT_ID>2</ATT_ID>
  <KeyAttribute>0</KeyAttribute>
  <Order>2</Order>

    <VAL_ID>32614</VAL_ID>
    <Name>Design</Name>
    <Value>asfdsa</Value>
    <GroupName>zOther</GroupName>

</AT>

Upvotes: 1

Views: 117

Answers (2)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

Not sure if it will work, because I don't have your table structure to test, but it gives results on my simplified demo. Try wrapping your query in CTE (or sub-query) and using SELECT ... FOR XML on it:

WITH AT AS 
(
    SELECT AT.ID ATT_ID
      ,VAL.ID VAL_ID
      ,COALESCE(ATT.Name,AT.Name) AS Name
      ,COALESCE(VALT.Value,VAL.Value) AS Value
      ,COALESCE(GT.Name,G.Name) AS GroupName
      ,AT.KeyAttribute
      ,AT.[Order]
      ,G.Order as G_Order --added for ordering
      ,G.Name as G_Name --added for ordering
    FROM    Attributes AT
      LEFT OUTER JOIN AttributesTranslations ATT on (AT.ID = ATT.AttributeID AND ATT.LanguageID = @LanguageID)
      LEFT OUTER JOIN AttributesValues VAL ON VAL.AttributeID = AT.ID AND VAL.ProductID = @ProductID
      LEFT OUTER JOIN AttributesValuesTranslations VALT on (VAL.ID = VALT.AttributeValueID AND VALT.LanguageID = @LanguageID)
      LEFT OUTER JOIN AttributesGroups G ON G.ID = AT.GroupID
      LEFT OUTER JOIN AttributesGroupsTranslations GT on (G.ID = GT.AttributeGroupID AND GT.LanguageID = @LanguageID)
    WHERE   AT.ProductTypeID = (SELECT ProductTypeID FROM Products WHERE ID = @ProductID)
       AND ((AT.RetailerID = @RetailerID) OR (@RetailerID = -1))
)
SELECT 
    ATT_ID ,
    KeyAttribute,
    [Order],
    VAL_ID ,
    Name ,
    Value ,
    GroupName  
FROM AT
ORDER BY G_Order,G_Name,[Order]
FOR XML AUTO, ELEMENTS

Simplified DEMO

Upvotes: 3

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Here's a simplified (uglified) version: wrap everything in a select and do FOR XML of that:

SELECT * FROM (
  ...your-select-here...
) temp
FOR XML AUTO, ELEMENTS

Upvotes: 2

Related Questions