XML FOR PATH with CDATA

Standard

Recently I had to put together a result set in XML format. There was only one root node called Products and each product sat under that node. Easy. Let’s do it:

Create some dummy data

DECLARE @xmltable TABLE
(
ProductId INTEGER IDENTITY(1,1),
Name VARCHAR(100) NOT NULL,
[Description] VARCHAR(100) NOT NULL
)

INSERT INTO @xmltable VALUES ('Intoxotil','Best synthetic alcohol there is.'),
('Somnnolax','The only sleep laxative on the market.'),
('Carajax','To help you get the car you want, fast.'),
('Pisciscent','Attractant for partners heavily into fishing'),
('Keithomil','Helping non-Keiths change to Keiths since 1956')


SELECT *
FROM @xmltable

Which returns

FORXML1

Standard stuff agreed. Now let’s return in in XML format:

SELECT *
FROM @xmltable
FOR XML PATH('Product'), ROOT('Products'), ELEMENTS

Which returns the following XML:

<Products>
  <Product>
    <ProductId>1</ProductId>
    <Name>Intoxotil</Name>
    <Description>Best synthetic alcohol there Is.</Description>
  </Product>
  <Product>
    <ProductId>2</ProductId>
    <Name>Somnnolax</Name>
    <Description>The only sleep laxative On the market.</Description>
  </Product>
  <Product>
    <ProductId>3</ProductId>
    <Name>Carajax</Name>
    <Description>To help you Get the car you want, fast.</Description>
  </Product>
  <Product>
    <ProductId>4</ProductId>
    <Name>Pisciscent</Name>
    <Description>Attractant For partners heavily into fishing</Description>
  </Product>
  <Product>
    <ProductId>5</ProductId>
    <Name>Keithomil</Name>
    <Description>Helping non-Keiths change To Keiths since 1956</Description>
  </Product>
</Products>

Cool story bro, right?

The problem was that we needed to return the Description in CDATA tags. Which is where things get more interesting. Basically because (as far as I’m aware) FOR XML PATH doesn’t allow the inclusion of CDATA tags.

The way to resolve this is by using FOR XML EXPLICIT. And the way to do it is below:

SELECT 1       [Tag],
       NULL    [Parent],
       NULL    [Products!1!productid],
       NULL    [Product!2!productid],
       NULL    [Product!2!description!cdata]

UNION ALL

SELECT 2,
       1,
       x.ProductId,
       x.ProductId,
       x.[Description]
FROM @xmltable x
FOR XML EXPLICIT

Which returns us the values we were after including the CDATA tags.

<Products>
  <Product productid="1">
    <description><![CDATA[Best synthetic alcohol there is.]]></description>
  </Product>
  <Product productid="2">
    <description><![CDATA[The only sleep laxative on the market.]]></description>
  </Product>
  <Product productid="3">
    <description><![CDATA[To help you get the car you want, fast.]]></description>
  </Product>
  <Product productid="4">
    <description><![CDATA[Attractant for partners heavily into fishing]]></description>
  </Product>
  <Product productid="5">
    <description><![CDATA[Helping non-Keiths change to Keiths since 1956]]></description>
  </Product>
</Products>


Yes, it’s a bit of a pain having to work around, but still it’s manageable. And I probably spend more time writing this blog post than I did working around the problem…

Advertisements