Solved

T SQL group values into XML data field

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

920 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

15 Experts available now in Live!

Get 1:1 Help Now