Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting rows to XML format in SQL Server

I have requirement like below.

enter image description here

And ddl and dml script for above image is

CREATE TABLE #example
    ([CCP_DETAILS_SID] int, [ACCOUNT_GROWTH] int, [PRODUCT_GROWTH] int, [PROJECTION_SALES] numeric(22,6), [PROJECTION_UNITS] numeric(22,6), [PERIOD_SID] int)
;

INSERT INTO #example
    ([CCP_DETAILS_SID], [ACCOUNT_GROWTH], [PRODUCT_GROWTH], [PROJECTION_SALES], [PROJECTION_UNITS], [PERIOD_SID])
VALUES
    (30001, 0, 0, 1505384.695, 18487.25251, 1801),
    (30001, 0, 0, 1552809.983, 18695.75536, 1802),
    (30001, 0, 0, 1595642.121, 18834.75725, 1803),
    (30002, 0, 0, 10000.32, 18834.75725, 1801),
    (30002, 0, 0, 1659124.98, 18834.75725, 1802),
    (30002, 0, 0, 465859546.6, 18834.75725, 1803)
;

And i have to convert above results to xml format like below (Output).

ccp_details_sid           xml_format_string

30001                      <period>  
                           <period_sid period_sid=1801>
                           <PROJECTION_SALES>1505384.695</PROJECTION_SALES>
                           <PROJECTION_UNITS>18487.25251<PROJECTION_UNITS>
                           <ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
                           <PRODUCT_GROWTH>0</PRODUCT_GROWTH>
                           </period_sid>
                            <period_sid period_sid=1802>
                           <PROJECTION_SALES>1552809.983</PROJECTION_SALES>
                           <PROJECTION_UNITS>18695.75536<PROJECTION_UNITS>
                           <ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
                           <PRODUCT_GROWTH>0</PRODUCT_GROWTH>
                           </period_sid>
                           <period_sid period_sid=1802>
                           <PROJECTION_SALES>1595642.121</PROJECTION_SALES>
                           <PROJECTION_UNITS>18834.75725<PROJECTION_UNITS>
                           <ACCOUNT_GROWTH>0</ACCOUNT_GROWTH>
                           <PRODUCT_GROWTH>0</PRODUCT_GROWTH>
                           </period_sid>
                           </period>

30002                      Same like above      

I am new to XML so couldn't able to do it quickly. I have used Marc_s solution with cross apply but can't able to achieve it.

Note: my major goal is, in above image if we see there are three records for single ccp_details_sid so I want to convert it as one row by using XML (mentioned above).

like image 544
Tharunkumar Reddy Avatar asked Jun 30 '17 07:06

Tharunkumar Reddy


People also ask

How can I get SQL query results in XML?

You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.

How can we generate XML from data in table?

AUTO mode is one of the approaches to converts over to the XML elements from the rows of the table. Element names of the XML document corresponding to the column alias or column name of the SELECT query. For example, the query result has been produced with a single XML document for the above sample data.


1 Answers

The following will work for you:

SELECT  t.CCP_DETAILS_SID,
        (   SELECT  PERIOD_SID AS [@period_sid],
                    x.PROJECTION_SALES,
                    x.PROJECTION_UNITS,
                    x.ACCOUNT_GROWTH,
                    x.PRODUCT_GROWTH
            FROM    #Example AS x
            WHERE   x.CCP_DETAILS_SID = t.CCP_DETAILS_SID
            FOR XML PATH('period_sid'), TYPE, ROOT('period')
        ) AS xml_format_string
FROM    #Example AS t
GROUP BY t.CCP_DETAILS_SID;

It essentially gets all your unique values for CCP_DETAILS_SID using:

SELECT  t.CCP_DETAILS_SID
FROM    #Example AS t
GROUP BY t.CCP_DETAILS_SID;

Then for each of these values uses the correlated subquery to form the XML. With the key points being:

  • Use @ in front of the alias to create a property, e.g. AS [@period_sid]
  • Use PATH('period_sid') to name the container for each row
  • Use ROOT('period') to name the outer nodes.

Example on DBFiddle

like image 172
GarethD Avatar answered Sep 20 '22 22:09

GarethD