locked
Need sql to generate xml structure RRS feed

  • Question

  • Hi there,

    i have sql data like below. If you can see, There are few repeating BatchIDs (aa,bb,cc)

    ID    BatchID    Location    Email
    1      aa        dfd       a@a.com
    2      bb        greg       b@b.com
    3      aa        dd       c@c.com
    4      cc        bd       a@a.com
    5      bb        ef       b@b.com
    6      cc        yy       c@c.com
    7      aa        gs       a@a.com
    8      bb        df       b@b.com
    9      aa        t       c@c.com

    now in the select statement i want to do following things

    1. create the following structure and create a node say <chunks> for every batch id data

       

    <Root> <data> <Chunks> <Chunk> <Id>value here</ID> <BatchId>aa<BatchId> <Location>value here </Location> <email>value here</email> </Chunk> <Chunk> <Id>value here</ID> <BatchId>aa<BatchId> <Location>value here </Location> <email>value here</email> </Chunk> </Chunks> <Chunks> <Chunk> <Id>value here</ID> <BatchId>aa<BatchId> <Location>value here </Location> <email>value here</email> </Chunk> <Chunk> <Id>value here</ID> <BatchId>aa<BatchId> <Location>value here </Location> <email>value here</email> </Chunk> </Chunks>

    <Chunks>
         <Chunk>
             <Id>value here</ID>
             <BatchId>cc<BatchId>
             <Location>value here </Location>
             <email>value here</email>
         </Chunk>
         <Chunk>
             <Id>value here</ID>
             <BatchId>cc<BatchId>
             <Location>value here </Location>
             <email>value here</email>
         </Chunk>
       </Chunks>

    </data> </Root>


    so there are two repeating nodes, Chunks, Chunk.

    Inside Chunks, data related to one BatchId will have to contain under <chunk> which is repeating.

    so for another batch id, another Chunks record should generate and inside that data related to that BatchId has to contain

    2. Now the second need is, if the number of records for a particular Batchid exceeds say 3, actually for each 3 similar batchid  records another Chunks record should be generated and those 3 records should be included inside this record.

    Sorry i may be confusing, some how i need to come up with a structure that, 

    every batch id data should go under new node, and for every batch only x number of records should be included, and rest should go under new record.

    any idea how to approach to achieve this


    Please mark the post as answer if this answers your question. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.



    Friday, April 6, 2018 2:01 AM

Answers

  • Hi Ravindar,

    Please check it out and see if it is working for you.

    SQL:

    DECLARE @tbl TABLE (
       ID INT NOT NULL
       , BatchID VARCHAR(2) NOT NULL
       , [Location] VARCHAR(100) NOT NULL
       , Email VARCHAR(100) NOT NULL
    );
    INSERT INTO @tbl
    VALUES (1, 'aa', 'dfd', 'a@a.com')
    , (2, 'bb', 'greg', 'b@b.com')
    , (3, 'aa', 'dd', 'c@c.com')
    , (4, 'cc', 'bd', 'a@a.com')
    , (5, 'bb', 'ef', 'b@b.com')
    , (6, 'cc', 'yy', 'c@c.com')
    , (7, 'aa', 'gs', 'a@a.com')
    , (8, 'bb', 'df', 'b@b.com')
    , (9, 'aa', 't', 'c@c.com');
    
    DECLARE @xml XML = '<root><data></data></root>';
    
    DECLARE @BatchNo INT
    	, @RowsPerBatch INT = 3
    	, @RowTotal INT
    	, @NumberOfBatchesTotal DECIMAL(12,0)
    	, @XMLFragment XML = '';
    
    -- To calculate # of Batches
    SET @RowTotal = (SELECT COUNT(*) FROM @tbl);
    SET @NumberOfBatchesTotal = CEILING(@RowTotal/@RowsPerBatch);
    
    SET @BatchNo = 1;
    
    WHILE @BatchNo <= @NumberOfBatchesTotal
    BEGIN
    	SET @XMLFragment = (SELECT *
    		FROM @tbl
    		ORDER BY BatchID, ID
    			OFFSET (@BatchNo - 1) * @RowsPerBatch ROWS
    			FETCH NEXT @RowsPerBatch ROWS ONLY
    		FOR XML PATH('Chunk'), TYPE, ROOT('Chunks')
    		);
    
    	-- add current XML fragment
    	SET @xml.modify('            
    		insert sql:variable("@XMLFragment")
    		as last
    		into (/root/data)[1]');    
    
       SET @BatchNo += 1;
    END;
    
    SELECT @xml;
     

    Output:

    <root>
      <data>
        <Chunks>
          <Chunk>
            <ID>1</ID>
            <BatchID>aa</BatchID>
            <Location>dfd</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>3</ID>
            <BatchID>aa</BatchID>
            <Location>dd</Location>
            <Email>c@c.com</Email>
          </Chunk>
          <Chunk>
            <ID>7</ID>
            <BatchID>aa</BatchID>
            <Location>gs</Location>
            <Email>a@a.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>9</ID>
            <BatchID>aa</BatchID>
            <Location>t</Location>
            <Email>c@c.com</Email>
          </Chunk>
          <Chunk>
            <ID>2</ID>
            <BatchID>bb</BatchID>
            <Location>greg</Location>
            <Email>b@b.com</Email>
          </Chunk>
          <Chunk>
            <ID>5</ID>
            <BatchID>bb</BatchID>
            <Location>ef</Location>
            <Email>b@b.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>8</ID>
            <BatchID>bb</BatchID>
            <Location>df</Location>
            <Email>b@b.com</Email>
          </Chunk>
          <Chunk>
            <ID>4</ID>
            <BatchID>cc</BatchID>
            <Location>bd</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>6</ID>
            <BatchID>cc</BatchID>
            <Location>yy</Location>
            <Email>c@c.com</Email>
          </Chunk>
        </Chunks>
      </data>
    </root>


    Monday, April 9, 2018 5:06 PM
  • Much simpler method without any loop

    DECLARE @tbl TABLE (
       ID INT NOT NULL
       , BatchID VARCHAR(2) NOT NULL
       , [Location] VARCHAR(100) NOT NULL
       , Email VARCHAR(100) NOT NULL
    );
    INSERT INTO @tbl
    VALUES (1, 'aa', 'dfd', 'a@a.com')
    , (2, 'bb', 'greg', 'b@b.com')
    , (3, 'aa', 'dd', 'c@c.com')
    , (4, 'cc', 'bd', 'a@a.com')
    , (5, 'bb', 'ef', 'b@b.com')
    , (6, 'cc', 'yy', 'c@c.com')
    , (7, 'aa', 'gs', 'a@a.com')
    , (8, 'bb', 'df', 'b@b.com')
    , (9, 'aa', 't', 'c@c.com');
    
    ;With CTE
    AS
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY ID) AS Seq,
    *
    FROM @tbl
    )
    SELECT (
    SELECT  (SELECT ID,BatchID,Location,Email
    FROM CTE
    WHERE (Seq-1)/2 = Grp
    AND BatchID = t.BatchID
    FOR XML PATH('Chunk'),TYPE
    ) AS [*]
    FROM (SELECT DISTINCT (Seq-1)/2 AS Grp,BatchID FROM CTE)t
    FOR XML PATH('Chunks'),ROOT('data'),TYPE
    ) AS [*]
    FOR XML PATH('Root')
    
    
    
    
    /*
    Output
    -----------------------------------
    <Root>
      <data>
        <Chunks>
          <Chunk>
            <ID>1</ID>
            <BatchID>aa</BatchID>
            <Location>dfd</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>3</ID>
            <BatchID>aa</BatchID>
            <Location>dd</Location>
            <Email>c@c.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>2</ID>
            <BatchID>bb</BatchID>
            <Location>greg</Location>
            <Email>b@b.com</Email>
          </Chunk>
          <Chunk>
            <ID>5</ID>
            <BatchID>bb</BatchID>
            <Location>ef</Location>
            <Email>b@b.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>4</ID>
            <BatchID>cc</BatchID>
            <Location>bd</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>6</ID>
            <BatchID>cc</BatchID>
            <Location>yy</Location>
            <Email>c@c.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>7</ID>
            <BatchID>aa</BatchID>
            <Location>gs</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>9</ID>
            <BatchID>aa</BatchID>
            <Location>t</Location>
            <Email>c@c.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>8</ID>
            <BatchID>bb</BatchID>
            <Location>df</Location>
            <Email>b@b.com</Email>
          </Chunk>
        </Chunks>
      </data>
    </Root>
    */

    Also refer

    https://visakhm.blogspot.com/2014/05/t-sql-tips-fun-with-for-xml-path.html

    https://visakhm.blogspot.ae/2013/12/generating-nested-xml-structures-with.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 9, 2018 6:01 PM

All replies

  • Hi Ravindar,

    Please check it out and see if it is working for you.

    SQL:

    DECLARE @tbl TABLE (
       ID INT NOT NULL
       , BatchID VARCHAR(2) NOT NULL
       , [Location] VARCHAR(100) NOT NULL
       , Email VARCHAR(100) NOT NULL
    );
    INSERT INTO @tbl
    VALUES (1, 'aa', 'dfd', 'a@a.com')
    , (2, 'bb', 'greg', 'b@b.com')
    , (3, 'aa', 'dd', 'c@c.com')
    , (4, 'cc', 'bd', 'a@a.com')
    , (5, 'bb', 'ef', 'b@b.com')
    , (6, 'cc', 'yy', 'c@c.com')
    , (7, 'aa', 'gs', 'a@a.com')
    , (8, 'bb', 'df', 'b@b.com')
    , (9, 'aa', 't', 'c@c.com');
    
    DECLARE @xml XML = '<root><data></data></root>';
    
    DECLARE @BatchNo INT
    	, @RowsPerBatch INT = 3
    	, @RowTotal INT
    	, @NumberOfBatchesTotal DECIMAL(12,0)
    	, @XMLFragment XML = '';
    
    -- To calculate # of Batches
    SET @RowTotal = (SELECT COUNT(*) FROM @tbl);
    SET @NumberOfBatchesTotal = CEILING(@RowTotal/@RowsPerBatch);
    
    SET @BatchNo = 1;
    
    WHILE @BatchNo <= @NumberOfBatchesTotal
    BEGIN
    	SET @XMLFragment = (SELECT *
    		FROM @tbl
    		ORDER BY BatchID, ID
    			OFFSET (@BatchNo - 1) * @RowsPerBatch ROWS
    			FETCH NEXT @RowsPerBatch ROWS ONLY
    		FOR XML PATH('Chunk'), TYPE, ROOT('Chunks')
    		);
    
    	-- add current XML fragment
    	SET @xml.modify('            
    		insert sql:variable("@XMLFragment")
    		as last
    		into (/root/data)[1]');    
    
       SET @BatchNo += 1;
    END;
    
    SELECT @xml;
     

    Output:

    <root>
      <data>
        <Chunks>
          <Chunk>
            <ID>1</ID>
            <BatchID>aa</BatchID>
            <Location>dfd</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>3</ID>
            <BatchID>aa</BatchID>
            <Location>dd</Location>
            <Email>c@c.com</Email>
          </Chunk>
          <Chunk>
            <ID>7</ID>
            <BatchID>aa</BatchID>
            <Location>gs</Location>
            <Email>a@a.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>9</ID>
            <BatchID>aa</BatchID>
            <Location>t</Location>
            <Email>c@c.com</Email>
          </Chunk>
          <Chunk>
            <ID>2</ID>
            <BatchID>bb</BatchID>
            <Location>greg</Location>
            <Email>b@b.com</Email>
          </Chunk>
          <Chunk>
            <ID>5</ID>
            <BatchID>bb</BatchID>
            <Location>ef</Location>
            <Email>b@b.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>8</ID>
            <BatchID>bb</BatchID>
            <Location>df</Location>
            <Email>b@b.com</Email>
          </Chunk>
          <Chunk>
            <ID>4</ID>
            <BatchID>cc</BatchID>
            <Location>bd</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>6</ID>
            <BatchID>cc</BatchID>
            <Location>yy</Location>
            <Email>c@c.com</Email>
          </Chunk>
        </Chunks>
      </data>
    </root>


    Monday, April 9, 2018 5:06 PM
  • Much simpler method without any loop

    DECLARE @tbl TABLE (
       ID INT NOT NULL
       , BatchID VARCHAR(2) NOT NULL
       , [Location] VARCHAR(100) NOT NULL
       , Email VARCHAR(100) NOT NULL
    );
    INSERT INTO @tbl
    VALUES (1, 'aa', 'dfd', 'a@a.com')
    , (2, 'bb', 'greg', 'b@b.com')
    , (3, 'aa', 'dd', 'c@c.com')
    , (4, 'cc', 'bd', 'a@a.com')
    , (5, 'bb', 'ef', 'b@b.com')
    , (6, 'cc', 'yy', 'c@c.com')
    , (7, 'aa', 'gs', 'a@a.com')
    , (8, 'bb', 'df', 'b@b.com')
    , (9, 'aa', 't', 'c@c.com');
    
    ;With CTE
    AS
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY ID) AS Seq,
    *
    FROM @tbl
    )
    SELECT (
    SELECT  (SELECT ID,BatchID,Location,Email
    FROM CTE
    WHERE (Seq-1)/2 = Grp
    AND BatchID = t.BatchID
    FOR XML PATH('Chunk'),TYPE
    ) AS [*]
    FROM (SELECT DISTINCT (Seq-1)/2 AS Grp,BatchID FROM CTE)t
    FOR XML PATH('Chunks'),ROOT('data'),TYPE
    ) AS [*]
    FOR XML PATH('Root')
    
    
    
    
    /*
    Output
    -----------------------------------
    <Root>
      <data>
        <Chunks>
          <Chunk>
            <ID>1</ID>
            <BatchID>aa</BatchID>
            <Location>dfd</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>3</ID>
            <BatchID>aa</BatchID>
            <Location>dd</Location>
            <Email>c@c.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>2</ID>
            <BatchID>bb</BatchID>
            <Location>greg</Location>
            <Email>b@b.com</Email>
          </Chunk>
          <Chunk>
            <ID>5</ID>
            <BatchID>bb</BatchID>
            <Location>ef</Location>
            <Email>b@b.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>4</ID>
            <BatchID>cc</BatchID>
            <Location>bd</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>6</ID>
            <BatchID>cc</BatchID>
            <Location>yy</Location>
            <Email>c@c.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>7</ID>
            <BatchID>aa</BatchID>
            <Location>gs</Location>
            <Email>a@a.com</Email>
          </Chunk>
          <Chunk>
            <ID>9</ID>
            <BatchID>aa</BatchID>
            <Location>t</Location>
            <Email>c@c.com</Email>
          </Chunk>
        </Chunks>
        <Chunks>
          <Chunk>
            <ID>8</ID>
            <BatchID>bb</BatchID>
            <Location>df</Location>
            <Email>b@b.com</Email>
          </Chunk>
        </Chunks>
      </data>
    </Root>
    */

    Also refer

    https://visakhm.blogspot.com/2014/05/t-sql-tips-fun-with-for-xml-path.html

    https://visakhm.blogspot.ae/2013/12/generating-nested-xml-structures-with.html


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 9, 2018 6:01 PM
  • Thank you both, the approach has worked, i changed it the way i wanted it. thank you very much.

    Please mark the post as answer if this answers your question. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply.

    Tuesday, April 10, 2018 4:14 AM