Solved

T SQL group values into XML data field

Posted on 2014-01-23
5
550 Views
Last Modified: 2014-01-24
I have a result set:
SECTION      Start_point      End_Point      DEFECT_CODE
SecA      0.00      50.00      RUT
SecA      0.00      50.00      FAT
SecB      0.00      50.00      RUT
SecB      0.00      50.00      CRACK
SecC      0.00      50.00      FRET
SecC      0.00      50.00      RUT
SecC      0.00      50.00      FAT
SecA      50.00      100.00      RUT
SecA      50.00      100.00      FAT
SecA      50.00      100.00      POTHOLE
SecB      50.00      100.00      RUT
SecB      50.00      100.00      CRACK
SecB      50.00      100.00      FRET
SecC      50.00      100.00      FRET
SecC      50.00      100.00      RUT
SecC      50.00      100.00      FAT
SecA      100.00      150.00      RUT
SecA      100.00      150.00      FAT
SecB      100.00      150.00      FRET
SecC      100.00      150.00      FRET
SecC      100.00      150.00      RUT
SecC      100.00      150.00      FAT
SecC      100.00      150.00      FAT
SecC      150.00      200.00      FRET
SecC      150.00      200.00      RUT
SecC      150.00      200.00      POTHOLE
SecC      150.00      200.00      FAT
SecC      200.00      250.00      RUT
SecC      200.00      250.00      POTHOLE
SecC      200.00      250.00      FAT


I need to group this into a table
DECLARE @OutputChoppedDefectXml TABLE
(
       SECTION                  nvarchar(30)      NOT NULL
      ,START_POINT            decimal(9,2)      NOT NULL
      ,END_POINT                  decimal(9,2)      NOT NULL
      ,DEFECT_CODES_XML      xml                        NOT NULL
);

so that output looks roughly like


INSERT INTO @ExampleChoppedDefectXml (SECTION, START_POINT, END_POINT, DEFECT_CODES_XML)
VALUES
       ('SecA', 0, 10, '<DefectCodes>RUT</DefectCodes>')
      ,('SecA', 10, 20, '<DefectCodes>RUT,POTHOLE</DefectCodes>')
      ,('SecA', 20, 50, '<DefectCodes>RUT</DefectCodes>')
      ,('SecA', 50, 60, '<DefectCodes>RUT,FAT</DefectCodes>')
      ,('SecA', 60, 70, '<DefectCodes>RUT,POTHOLE,FAT</DefectCodes>')
      ,('SecA', 70, 100, '<DefectCodes>RUT,FAT</DefectCodes>')
      ,('SecA', 100, 140, '<DefectCodes>FAT</DefectCodes>');


The Defect codes must be ordered according to a table @DefectCode table which has a rank assigned to each code.

Please help with this.
0
Comment
Question by:DanP1980
  • 3
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39803256
this is possible, even using different techniques.
I usually use the one with FOR XML PATH('') in a subquery, see here for the different techniques exposed:
http://anexinetbi.blogspot.be/2013/12/groupconcat-functionality-for-sql-server.html
0
 

Author Comment

by:DanP1980
ID: 39803356
Thanks Guy sadly my proxy server will block this:

I have written the following which works however it does not add the XML node <DefectCodes> around the values.  I end up with

Select Section, Start_point, End_Point,  
      SUBSTRING (
      (Select ( ',' + t2.DEFECT_CODE)
      
      From #Stage1 t2
      Inner join  @DefectCode dc on dc.DEFECT_CODE = t2.DEFECT_CODE
      Where t1.SECTION = t2.Section and t1.Start_Point = t2.Start_Point and t1.End_Point = t2.End_point
      
      Order by CODE_ORDER, SECTION, Start_Point, End_Point
      for XML Path( '' )
                                    ),2,1000)
                                    
                                    from #Stage1 t1
                                    
      Group by Section, Start_point, End_Point


Section      Start_point      End_Point      (No column name)
SecA      0.00      50.00      RUT,FAT
SecA      50.00      100.00      RUT,POTHOLE,FAT
SecA      100.00      150.00      RUT,FAT
SecB      0.00      50.00      CRACK,RUT
SecB      50.00      100.00      FRET,CRACK,RUT
SecB      100.00      150.00      FRET
SecC      0.00      50.00      FRET,RUT,FAT
SecC      50.00      100.00      FRET,RUT,FAT
SecC      100.00      150.00      FRET,RUT,FAT,FAT
SecC      150.00      200.00      FRET,RUT,POTHOLE,FAT
SecC      200.00      250.00      RUT,POTHOLE,FAT
0
 

Author Comment

by:DanP1980
ID: 39803418
I have found a solution in the end.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39805681
try like this.
SELECT [Section], 
       Start_point, 
       End_Point, 
       '<DefectCodes>' + DEFECT_CODES_XML 
       + '</DefectCodes>' AS DEFECT_CODES_XML 
  FROM (SELECT [Section], 
               Start_point, 
               End_Point, 
               SUBSTRING ((SELECT ( ',' + t2.DEFECT_CODE ) 
                             FROM #Stage1 t2 
                                  INNER JOIN @DefectCode dc 
                                          ON dc.DEFECT_CODE = t2.DEFECT_CODE 
                            WHERE t1.SECTION = t2.Section 
                              AND t1.Start_Point = t2.Start_Point 
                              AND t1.End_Point = t2.End_point 
                            ORDER BY CODE_ORDER, 
                                     SECTION, 
                                     Start_Point, 
                                     End_Point 
                           FOR XML Path( '' )), 2, 1000) AS DEFECT_CODES_XML 
          FROM #Stage1 t1 
         GROUP BY Section, 
                  Start_point, 
                  End_Point) T1 

Open in new window

0
 

Author Closing Comment

by:DanP1980
ID: 39806041
This is what I had come up with so it is good to get confirmation!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
LTrim & Double Space Correction 5 40
SQL Query 2 31
Dynamic SQL select query 4 37
shrink table after huge delete 2 14
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question