Temp Table to XML structure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Temp Table to XML structure

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2011-05-29 : 02:20:07

Friends,

I have temp table , i want to convert this to XML structure. Please find my sample and expected output

TempTable

ProductId ProductName UnitPrice
1001 ABC 2001
1002 XYZ 2300
1006 HJS 700

Expected XML value

<Products>
<Product>
<ProductId>1001</ProductId>
<ProductName>ABC</ProductName>
<UnitPrice>2001</UnitPrice>
</Product>

<Product>
<ProductId>1002</ProductId>
<ProductName>XYZ</ProductName>
<UnitPrice>2300</UnitPrice>
</Product>

<Product>
<ProductId>1006</ProductId>
<ProductName>HJS</ProductName>
<UnitPrice>700</UnitPrice>
</Product>

</Products>

Thanks.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-05-29 : 08:57:03

try using the bcp command

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-30 : 11:41:07

You can do it using xml auto as shown below.


select * from YourTempTable as Product for xml auto, elements, root('Products');

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources