Solved

T SQL group values into XML data field

Posted on 2014-01-23
5
554 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 41

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

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

695 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