locked
SQL Server - Group By Sets as XML Elements and sub Elements RRS feed

  • Question

  • Dear Experts, 

    I have a Requirement to convert the result into XML with the sub- elements in the set. Here are more details 

    i have a table as follows. 

     

    create table LaneDetails 
    (Name nvarchar(20),  StartPoint nvarchar(max),  Endpoint nvarchar(max), Lane int, Mode nvarchar(10)
    )

    insert into LaneDetails values ('abc', 'Startpoint 1.0', 'END point 1.0', 1, 'IN')
    insert into LaneDetails values ('abc', 'Startpoint 1.0', 'END point 1.0', 2, 'IN') 
    insert into LaneDetails values ('abc', 'Startpoint 1.0', 'END point 1.0', 3, 'IN') 
    insert into LaneDetails values ('abc', 'Startpoint 1.0', 'END point 1.0', 4, 'IN') 
    insert into LaneDetails values ('abc', 'Startpoint 1.0', 'END point 1.0', 1, 'OUT')
    insert into LaneDetails values ('abc', 'Startpoint 1.0', 'END point 1.0', 2, 'OUT') 
    insert into LaneDetails values ('abc', 'Startpoint 1.0', 'END point 1.0', 3, 'OUT') 
    insert into LaneDetails values ('abc', 'Startpoint 1.0', 'END point 1.0', 4, 'OUT') 

    End Result of the XML should be something like below,

    <reply xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <reply>
      <Name>"abc"</Name>
      <Startpoint> "Startpoint 1.0" </Startpoint>
      <ENDpoint> "ENDpoint 1.0" </ENDpoint>
      <Lanes>
      <IN>
      <Identifier>1 </Identifier> -- LaneNumber
       <Count> 10 </Count> -- LaneCount
      <Identifier> 2 </Identifier>
      <Count> 10 </Count> -- 
      <Identifier>3 </Identifier>
      <Count> 10 </Count> 
      </IN>
      <OUT>
      <Identifier>1 </Identifier> -- LaneNumber
      <Count> 10 </Count> -- LaneCount
      <Identifier> 2 </Identifier>
      <Count> 10 </Count> --
      <Identifier>3 </Identifier>
      <Count> 10 </Count>
      </OUT>
      </Lanes>
    </reply>
    </reply> 

    Any Help on the above could be greatly helpful. 

    Thanks

    Momen 


    Thank you... MOMEN

    Monday, August 3, 2020 7:40 AM

Answers

  • E.g.

    DECLARE @LaneDetails TABLE (
        Name NVARCHAR(20) ,
        StartPoint NVARCHAR(MAX) ,
        Endpoint NVARCHAR(MAX) ,
        Lane INT ,
        Mode NVARCHAR(10)
    );
    
    INSERT INTO @LaneDetails
    VALUES ( 'abc', 'Startpoint 1.0', 'END point 1.0', 11, 'IN' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 12, 'IN' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 13, 'IN' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 14, 'IN' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 21, 'OUT' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 22, 'OUT' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 23, 'OUT' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 24, 'OUT' );
    
    SELECT   LD.Name ,
             LD.StartPoint ,
             LD.Endpoint ,
             (   SELECT   LD_IN.Lane ,
                          COUNT(*) AS [Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'IN'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH(''), TYPE ) AS [IN] ,
             (   SELECT   LD_IN.Lane ,
                          COUNT(*) AS [Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'OUT'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH(''), TYPE ) AS [OUT]
    FROM     @LaneDetails LD
    GROUP BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    		 ORDER BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    FOR XML PATH('reply');
    
    SELECT   LD.Name ,
             LD.StartPoint ,
             LD.Endpoint ,
             (   SELECT   LD_IN.Lane AS [ID],
                          COUNT(*) AS [Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'IN'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH('Lane'), TYPE ) AS [IN] ,
             (   SELECT   LD_IN.Lane AS [ID],
                          COUNT(*) AS [Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'OUT'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH('Lane'), TYPE ) AS [OUT]
    FROM     @LaneDetails LD
    GROUP BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    		 ORDER BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    FOR XML PATH('reply');
    
    SELECT   LD.Name ,
             LD.StartPoint ,
             LD.Endpoint ,
             (   SELECT   LD_IN.Lane AS [@ID] ,
                          COUNT(*) AS [@Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'IN'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH('Lane'), TYPE ) AS [IN] ,
             (   SELECT   LD_IN.Lane AS [@ID] ,
                          COUNT(*) AS [@Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'out'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH('Lane'), TYPE ) AS [OUT]
    FROM     @LaneDetails LD
    GROUP BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    ORDER BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    FOR XML PATH('reply');

    Results in

    <reply>
      <Name>abc</Name>
      <StartPoint>Startpoint 1.0</StartPoint>
      <Endpoint>END point 1.0</Endpoint>
      <IN>
        <Lane>11</Lane>
        <Count>1</Count>
        <Lane>12</Lane>
        <Count>1</Count>
        <Lane>13</Lane>
        <Count>1</Count>
        <Lane>14</Lane>
        <Count>1</Count>
      </IN>
      <OUT>
        <Lane>21</Lane>
        <Count>1</Count>
        <Lane>22</Lane>
        <Count>1</Count>
        <Lane>23</Lane>
        <Count>1</Count>
        <Lane>24</Lane>
        <Count>1</Count>
      </OUT>
    </reply>

    and

    <reply>
      <Name>abc</Name>
      <StartPoint>Startpoint 1.0</StartPoint>
      <Endpoint>END point 1.0</Endpoint>
      <IN>
        <Lane>
          <ID>11</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>12</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>13</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>14</ID>
          <Count>1</Count>
        </Lane>
      </IN>
      <OUT>
        <Lane>
          <ID>21</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>22</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>23</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>24</ID>
          <Count>1</Count>
        </Lane>
      </OUT>
    </reply>

    and

    <reply>
      <Name>abc</Name>
      <StartPoint>Startpoint 1.0</StartPoint>
      <Endpoint>END point 1.0</Endpoint>
      <IN>
        <Lane ID="11" Count="1" />
        <Lane ID="12" Count="1" />
        <Lane ID="13" Count="1" />
        <Lane ID="14" Count="1" />
      </IN>
      <OUT>
        <Lane ID="21" Count="1" />
        <Lane ID="22" Count="1" />
        <Lane ID="23" Count="1" />
        <Lane ID="24" Count="1" />
      </OUT>
    </reply>



    Monday, August 3, 2020 10:17 AM

All replies

  • E.g.

    DECLARE @LaneDetails TABLE (
        Name NVARCHAR(20) ,
        StartPoint NVARCHAR(MAX) ,
        Endpoint NVARCHAR(MAX) ,
        Lane INT ,
        Mode NVARCHAR(10)
    );
    
    INSERT INTO @LaneDetails
    VALUES ( 'abc', 'Startpoint 1.0', 'END point 1.0', 11, 'IN' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 12, 'IN' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 13, 'IN' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 14, 'IN' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 21, 'OUT' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 22, 'OUT' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 23, 'OUT' ) ,
           ( 'abc', 'Startpoint 1.0', 'END point 1.0', 24, 'OUT' );
    
    SELECT   LD.Name ,
             LD.StartPoint ,
             LD.Endpoint ,
             (   SELECT   LD_IN.Lane ,
                          COUNT(*) AS [Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'IN'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH(''), TYPE ) AS [IN] ,
             (   SELECT   LD_IN.Lane ,
                          COUNT(*) AS [Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'OUT'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH(''), TYPE ) AS [OUT]
    FROM     @LaneDetails LD
    GROUP BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    		 ORDER BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    FOR XML PATH('reply');
    
    SELECT   LD.Name ,
             LD.StartPoint ,
             LD.Endpoint ,
             (   SELECT   LD_IN.Lane AS [ID],
                          COUNT(*) AS [Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'IN'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH('Lane'), TYPE ) AS [IN] ,
             (   SELECT   LD_IN.Lane AS [ID],
                          COUNT(*) AS [Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'OUT'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH('Lane'), TYPE ) AS [OUT]
    FROM     @LaneDetails LD
    GROUP BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    		 ORDER BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    FOR XML PATH('reply');
    
    SELECT   LD.Name ,
             LD.StartPoint ,
             LD.Endpoint ,
             (   SELECT   LD_IN.Lane AS [@ID] ,
                          COUNT(*) AS [@Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'IN'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH('Lane'), TYPE ) AS [IN] ,
             (   SELECT   LD_IN.Lane AS [@ID] ,
                          COUNT(*) AS [@Count]
                 FROM     @LaneDetails LD_IN
                 WHERE    LD_IN.Name = LD.Name
                          AND LD_IN.StartPoint = LD.StartPoint
                          AND LD_IN.Endpoint = LD.Endpoint
                          AND LD_IN.Mode = 'out'
                 GROUP BY LD_IN.Lane
    			 ORDER BY LD_IN.Lane
                 FOR XML PATH('Lane'), TYPE ) AS [OUT]
    FROM     @LaneDetails LD
    GROUP BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    ORDER BY LD.Name ,
             LD.StartPoint ,
             LD.Endpoint
    FOR XML PATH('reply');

    Results in

    <reply>
      <Name>abc</Name>
      <StartPoint>Startpoint 1.0</StartPoint>
      <Endpoint>END point 1.0</Endpoint>
      <IN>
        <Lane>11</Lane>
        <Count>1</Count>
        <Lane>12</Lane>
        <Count>1</Count>
        <Lane>13</Lane>
        <Count>1</Count>
        <Lane>14</Lane>
        <Count>1</Count>
      </IN>
      <OUT>
        <Lane>21</Lane>
        <Count>1</Count>
        <Lane>22</Lane>
        <Count>1</Count>
        <Lane>23</Lane>
        <Count>1</Count>
        <Lane>24</Lane>
        <Count>1</Count>
      </OUT>
    </reply>

    and

    <reply>
      <Name>abc</Name>
      <StartPoint>Startpoint 1.0</StartPoint>
      <Endpoint>END point 1.0</Endpoint>
      <IN>
        <Lane>
          <ID>11</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>12</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>13</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>14</ID>
          <Count>1</Count>
        </Lane>
      </IN>
      <OUT>
        <Lane>
          <ID>21</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>22</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>23</ID>
          <Count>1</Count>
        </Lane>
        <Lane>
          <ID>24</ID>
          <Count>1</Count>
        </Lane>
      </OUT>
    </reply>

    and

    <reply>
      <Name>abc</Name>
      <StartPoint>Startpoint 1.0</StartPoint>
      <Endpoint>END point 1.0</Endpoint>
      <IN>
        <Lane ID="11" Count="1" />
        <Lane ID="12" Count="1" />
        <Lane ID="13" Count="1" />
        <Lane ID="14" Count="1" />
      </IN>
      <OUT>
        <Lane ID="21" Count="1" />
        <Lane ID="22" Count="1" />
        <Lane ID="23" Count="1" />
        <Lane ID="24" Count="1" />
      </OUT>
    </reply>



    Monday, August 3, 2020 10:17 AM
  • You are Awesome ! Thanks a Ton

    Thank you... MOMEN

    Monday, August 3, 2020 11:03 AM