Timothy Graffham (9/19/2012)
I was very excited to go to my post this morning and see 8 responses. I thought I was going to get some real help. Instead, the first response was an attempt but didn't help really, and then everyone just used my post for a chat about general complaints with XML as a technology.Very disappointing, folks. Can you please have those sort of conversations in their own threads?
My format that I illustrated in the original question was not flexible. I MUST display this information in this format even if I have to loop through the data procedurally and dynamically build it as a string. It's an installed base issue that I have to support.
My question is, is it possible? Can anyone tell me how to do this?
It is possible to display your data in the manner you are seeking as a STRING that will look like XML but will not be compliant and will not render in SSMS. The method to get the result you requested requires some messy replaces since normal XML methods won't do it. If that will work for you then my example is below. Just in case you have some flexibility or need the data in XML-compliant format I've also provided a few formatting variations so you can see how they might be done.
DECLARE
@x XML
,@s VARCHAR(MAX)
DECLARE @TempTable TABLE
(
ID INT IDENTITY(1,1) NOT NULL
,Part VARCHAR(10)
,Color VARCHAR(10)
,Size VARCHAR(10)
)
INSERT INTO @TempTable
SELECT '123','blue','small'
UNION ALL
SELECT '124','black','medium'
UNION ALL
SELECT '125','red','large'
/* Note: The part number tag formatted as '<123>' is an invalid XML tag */
/* and SQL will not render. This will create a pseudo-XML string using the */
/* integer part number as tag (non-XML compliant) */
SET @x =
(
SELECT
CAST(Part AS VARCHAR(50)) AS 'StartPart'
,(SELECT
temp.Color AS 'Color'
,temp.Size AS 'Size'
,CAST(Part AS VARCHAR(50)) AS 'EndPart'
FOR XML PATH(''),TYPE
)
FROM
@TempTable AS temp
FOR XML PATH(''), ROOT('PartsList')
)
SET @s-2 = CONVERT(VARCHAR(MAX), @x)
SET @s-2 =
REPLACE(
REPLACE(
REPLACE(
REPLACE(@s,'</StartPart>','>')
,'<StartPart>','<')
,'</EndPart>','>')
,'<EndPart>','</')
SELECT @s-2 AS String_Result
<PartsList>
<123>
<Color>blue</Color>
<Size>small</Size>
</123>
<124>
<Color>black</Color>
<Size>medium</Size>
</124>
<125>
<Color>red</Color>
<Size>large</Size>
</125>
</PartsList>
Properly formatted XML examples
/* Adds ascending character value to part number to create */
/* valid XML tag and sets the part number as its own parent tag */
/* with 'Color' and 'Size' as child tags. */
SET @x =
(
SELECT
CAST(CHAR(temp.ID+64) AS VARCHAR(3))
+CAST(Part AS VARCHAR(50)) AS 'StartPart'
,(SELECT
temp.Color AS 'Color'
,temp.Size AS 'Size'
,CAST(CHAR(temp.ID+64) AS VARCHAR(3))
+CAST(Part AS VARCHAR(50)) AS 'EndPart'
FOR XML PATH(''),TYPE
)
FROM
@TempTable AS temp
FOR XML PATH(''), ROOT('PartsList')
)
SET @s-2 = CONVERT(VARCHAR(MAX), @x)
SET @s-2 =
REPLACE(
REPLACE(
REPLACE(
REPLACE(@s,'</StartPart>','>')
,'<StartPart>','<')
,'</EndPart>','>')
,'<EndPart>','</')
SET @x = CONVERT(XML, @s-2)
SELECT @x AS XML_Result
<PartsList>
<A123>
<Color>blue</Color>
<Size>small</Size>
</A123>
<B124>
<Color>black</Color>
<Size>medium</Size>
</B124>
<C125>
<Color>red</Color>
<Size>large</Size>
</C125>
</PartsList>
/* Sets part number as single un-nested 'Part' tag which is */
/* followed sequentially by 'Color' and 'Size' for each part number */
SET @x =
(
SELECT
CAST(Part AS VARCHAR(50)) AS 'Part'
,(SELECT
temp.Color AS 'Color'
,temp.Size AS 'Size'
FOR XML PATH(''),TYPE
)
FROM
@TempTable AS temp
FOR XML PATH(''), ROOT('PartsList')
)
SELECT
@x AS XML_Result
<PartsList>
<Part>123</Part>
<Color>blue</Color>
<Size>small</Size>
<Part>124</Part>
<Color>black</Color>
<Size>medium</Size>
<Part>125</Part>
<Color>red</Color>
<Size>large</Size>
</PartsList>
/* Sets parent 'Part' tag with 'Color' and 'Size' as child tags */
SET @x =
(
SELECT
(SELECT ISNULL (CAST(Part AS VARCHAR (50)),'') AS 'Number'
FOR XML PATH(''), TYPE)
,(SELECT ISNULL (CAST(Color AS VARCHAR (50)),'') AS 'Color'
FOR XML PATH(''), TYPE)
,(SELECT ISNULL (CAST(Size AS VARCHAR (50)),'') AS 'Size'
FOR XML PATH('') ,TYPE)
FROM @TempTable
FOR XML PATH('Part'), ROOT('PartsList')
)
SELECT @x AS XML_Result
<PartsList>
<Part>
<Number>123</Number>
<Color>blue</Color>
<Size>small</Size>
</Part>
<Part>
<Number>124</Number>
<Color>black</Color>
<Size>medium</Size>
</Part>
<Part>
<Number>125</Number>
<Color>red</Color>
<Size>large</Size>
</Part>
</PartsList>
/* Sets parent 'Part' tag with a property value 'number=[part number]' */
/* with 'Color' and 'Size' as child tags */
SET @x =
(
SELECT
(
SELECT
Part AS 'Part/@number'
,Color AS 'Part/Color'
,Size AS 'Part/Size'
FOR XML PATH(''), TYPE
)
FROM @TempTable
FOR XML PATH(''), ROOT('PartsList')
)
SELECT @x AS XML_Result
<PartsList>
<Part number="123">
<Color>blue</Color>
<Size>small</Size>
</Part>
<Part number="124">
<Color>black</Color>
<Size>medium</Size>
</Part>
<Part number="125">
<Color>red</Color>
<Size>large</Size>
</Part>
</PartsList>