Converting T-SQL query to XML result

Question

Post reply

Converting T-SQL query to XML result

redmittens7

SSChasing Mays

Points: 628
More actions
July 15, 2015 at 11:33 am

#300082

Hello Forum,
I'm brand new to XML and I'm struggling to modify the T-SQL query below to return the results as XML.
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119, 122);
The XML result document should have the following structure:
<Root>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
</Root>
Please let me know if you there's a great resource for those new to XML. Thank you in advance for your help.
RedMittens

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

That seems very similar to the example in BOL for Specifying XPath-like column names

You could even google the query and you'd get the references. 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sgmunson SSC Guru Points: 110640 More actions · Answer 2

redmittens7 (7/15/2015)
Hello Forum,
I'm brand new to XML and I'm struggling to modify the T-SQL query below to return the results as XML.
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID IN (119, 122);
The XML result document should have the following structure:
<Root>
<ProductModelData id="119">
<Name>Bike Wash</Name>
</ProductModelData>
<ProductModelData id="122">
<Name>All-Purpose Bike Stand</Name>
</ProductModelData>
</Root>
Please let me know if you there's a great resource for those new to XML. Thank you in advance for your help.
RedMittens

Try this on for size - it creates the sample data, so you'll just need to copy out the query portion and substitute back in your actual table name.

The FOR XML PATH construction is fairly easy way to get data into XML form. You should at least read the entry in Books Online for this.

DECLARE @ProductModel AS TABLE (

ProductModelID int,

[Name] varchar(25)

);

INSERT INTO @ProductModel (ProductModelID, [Name])

VALUES (119, 'Bike Wash'), (122, 'All-Purpose Bike Stand');

SELECT ProductModelID AS "@id", Name

FROM @ProductModel

WHERE ProductModelID IN (119, 122)

FOR XML PATH ('ProductModelData'), root ('Root');

EDIT: After looking more closely at my original query, I realized I didn't have quite the right XML format, and then I saw Luis' post, and sure enough, the answer to this is right there in Books Online (aka BOL). Thus I changed my query to the way I'd write the same query.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)