Solved

T SQL group values into XML data field

Posted on 2014-01-23
5
540 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 142

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now