XML FOR PATH with CDATA


lsdbtech

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'), 

View original post 440 more words

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s