Technical Article

Selecting XML out

,

I have a table with multiple rows for Description belonging to one Heading. For each row of data I need a .
The xml should look like this:

A heading
234567890123456789010
234567890123456789010
234567890123456789010
  ...(to )


A heading
234567890123456789010
234567890123456789010
  ...(to )

SET NOCOUNT ON
 
CREATE TABLE #FREEFORM(
 [PCLASS]VARCHAR(10) NULL,
 [HEADING]VARCHAR(50) NULL,
 [DESC]VARCHAR(50) NULL,
 [ID]INT  NULL,
 [COUNTER]INT  NULL
)
INSERT INTO #FREEFORM
 SELECT  
  '111',
  'This is the Heading 1',
  'This is the description line = 1.1',
  1,
  1
INSERT INTO #FREEFORM
 SELECT 
  '',
  '',
  'This is the description line = 1.2',
  1,
  2
INSERT INTO #FREEFORM
 SELECT 
  '',
  '',
  'This is the description line = 1.3',
  1,
  3
INSERT INTO #FREEFORM
 SELECT 
  '222',
  'This is the Heading 2',
  'This is the description line = 2.1',
  2,
  1
INSERT INTO #FREEFORM
 SELECT 
  '',
  '',
  'This is the description line = 2.2',
  2,
  2

SELECT 
1 AS TAG,
NULL AS PARENT,
[ID] AS [ENDORSEMENTRISKLOCATION-EW501!1!ID!HIDE],
NULL AS [ENDORSEMENTRISKLOCATION-EW501!1!!ELEMENT],
NULL AS [PCLASS!2!ID!HIDE],
NULL AS [PCLASS!2!!ELEMENT],
NULL AS [FREEFORMENDORSEMENT-EW502!3!ID!HIDE],
NULL AS [FREEFORMENDORSEMENT-EW502!3!!ELEMENT],
NULL AS [HEADING!4!ID!HIDE],
NULL AS [HEADING!4!!ELEMENT],
NULL AS [DESCRIPTION!5!LINE],
NULL AS [DESCRIPTION!5!!ELEMENT]
FROM #FREEFORM WHERE [PCLASS] <> ''
UNION ALL
SELECT 
2 AS TAG,
1 AS PARENT,
[ID] AS [ENDORSEMENTRISKLOCATION-EW501!1!ID!HIDE],
NULL AS [ENDORSEMENTRISKLOCATION-EW501!1!!ELEMENT],
[ID] AS [PCLASS!2!ID!HIDE],
[PCLASS] AS [PCLASS!2],
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM #FREEFORM WHERE [PCLASS] <> ''

UNION ALL
SELECT 
3 AS TAG,
1 AS PARENT,
[ID],
NULL,
[ID],
[PCLASS],
[ID],
NULL,
NULL,
NULL,
NULL,
NULL
FROM #FREEFORM WHERE [PCLASS] <> ''

UNION ALL
SELECT 
4 AS TAG,
3 AS PARENT,
[ID],
NULL,
[ID],
[PCLASS],
[ID],
NULL,
[ID],
[HEADING],
NULL,
NULL
FROM #FREEFORM WHERE [HEADING] <> ''

UNION ALL
SELECT 
5 AS TAG,
3 AS PARENT,
[ID],
NULL,
[ID],
[PCLASS],
[ID],
NULL,
[ID],
[HEADING],
[COUNTER],
[DESC]
FROM #FREEFORM WHERE [DESC] <> ''


ORDER BY [ENDORSEMENTRISKLOCATION-EW501!1!ID!HIDE],[PCLASS!2!ID!HIDE],[FREEFORMENDORSEMENT-EW502!3!ID!HIDE],[HEADING!4!ID!HIDE],[DESCRIPTION!5!LINE]--,[ENDORSEMENTRISKLOCATION-EW501!1!!ELEMENT],[PCLASS!2!!ELEMENT],[FREEFORMENDORSEMENT-EW502!3!!ELEMENT]
FOR XML EXPLICIT
--SELECT * FROM #FREEFORM
GO
TRUNCATE TABLE #FREEFORM
DROP TABLE #FREEFORM

Rate

Share

Share

Rate