Grouping XML Elements in FOR XML Clause

Question

I am trying to create a structure xml document from my temp table .The temp table is in the following format .

CREATE TABLE #Temp1 ( Name Char( 30 ), seqid integer, salary int );
INSERT INTO #Temp1 VALUES('DEAL' ,123,6)
INSERT INTO #Temp1 VALUES('DEAL' ,56,6)
INSERT INTO #Temp1 VALUES('TRACNHE' ,1253,56)
INSERT INTO #Temp1 VALUES('TRACNHE' ,5,65)
INSERT INTO #Temp1 VALUES('ASSET' ,56,23)

I am trying to create an xml format in the following form :

<Response>
<Deal>
 <seqid="123" salary="6" />
 <seqid="56" salary="6" />
<Deal>
<TRACNHE>
 <seqid="1253" salary="56"/>
 <seqid="5" salary="65"/>
</TRACNHE>
<ASSET>
 <seqid="56" salary="23"/>
</ASSET>
</Response>

SELECT Name, (SELECT SEQID FROM #TEMP1 T WHERE T.Name = T1.Name)
FROM (SELECT DISTINCT NAME FROM #TEMP1 ) T1
FOR XML PATH('rEPONSE')
DROP TABLE #Temp1


DROP TABLE #Temp1

I tried the above query but says that subquery returned more than 1 value Could you let me know as to what i am missing in this query . Is there a better way to handle this scenario.

Thanks in advance

Answer 1

based on your requirement, i'm seeing there are 2 types of complexities

• You are trying to get the xml with grouped items.
• For each group trying to create an xml element with two attributes without any proper name
  <seqid="1253" salary="56"/>
  instead of
  <ss seqid="1253" salary="56"/>

just look into this below query, it may help

SELECT 
    (SELECT
        seqid 'ss/@seqid'
       , salary 'ss/@salary'
      FROM Temp1 as t where t.Name = 'Deal'
      FOR XML PATH('Deal') , TYPE
    )  ,
    (SELECT
        seqid 'ss/@seqid'
       , salary 'ss/@salary'
      FROM Temp1 as t where t.Name = 'TRACNHE'
      FOR XML PATH('TRACNHE') , TYPE
    )   ,
    (SELECT
        seqid 'ss/@seqid'
       , salary 'ss/@salary'
      FROM Temp1 as t where t.Name = 'ASSET'
      FOR XML PATH('ASSET') , TYPE
    )  
FOR XML PATH(''), ROOT('Response');