FOR SQL XML format

When I run the following code:
SELECT CompanyName, ProductName, UnitsInStock, UnitsOnOrder
from Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID FOR XML auto
I get a continuous string that represents the xml results like this.
<Suppliers CompanyName="Exotic Liquids"><Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/><Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/><Products ProductName="Aniseed Syrup" UnitsInStock="13" UnitsOnOrder="70"/></Suppliers><Suppliers CompanyName="New Orleans Cajun Delights"><Products ProductName="Chef Anton's Cajun Seasoning" UnitsInStock="53" UnitsOnOrder="0"/><Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0" UnitsOnOrder="0"/></Suppliers><Suppliers CompanyName="Grandma Kelly's Homestead"><Products ProductName="Grandma's BoysenberrySpread" UnitsInStock="120" UnitsOnOrder="0"/><Products ProductName="Uncle Bob's Organic Dried Pears" UnitsInStock="15" UnitsOnOrder="0"/><Products ProductName="Northwoods Cranberry Sauce" UnitsInStock="6" UnitsOnOrder="0"/></Suppliers...
how do i get the reslut in the XML format like this with the indents and new lines(returns) after the xml objects instead of a single string?
<Suppliers CompanyName="Exotic Liquids">
<Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/>
<Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/>
<Products ProductName="Aniseed Syrup" UnitsInStock="13" UnitsOnOrder="70"/>
</Suppliers>
<Suppliers CompanyName="New Orleans Cajun Delights">
<Products ProductName="Chef Anton's Cajun Seasoning" UnitsInStock="53" UnitsOnOrder="0"/>
<Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0" UnitsOnOrder="0"/>
</Suppliers>...
I see all the examples like SQL parses out and format the xml in the results window
Thx
Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451
XML doesn't actually include indents and newlines - that's just a convenient
way to format it so we can read it more easily. Query Analyzer just displays
the XML stream that gets returned by the query - from an XML-aware client
application's point of view, this is fine; it's just not very
human-friendly! If you want to see it formatted you'd have to load it into
an XML editor that formats it for you (and bear in mind that FOR XML queries
return XML Fragments - not full documents, so there's no root element).
The good news is that the SQL Management Studio tool in SQL Server 2005
includes an XML editor, so you get the results of FOR XML queries nicely
formatted.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Ron Sissons" <RSISSONS@.rcoe.k12.ca.us> wrote in message
news:%23h9ImsJhEHA.396@.TK2MSFTNGP12.phx.gbl...
When I run the following code:
SELECT CompanyName, ProductName, UnitsInStock, UnitsOnOrder
from Products JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID
FOR XML auto
I get a continuous string that represents the xml results like this.
<Suppliers CompanyName="Exotic Liquids"><Products ProductName="Chai"
UnitsInStock="39" UnitsOnOrder="0"/><Products ProductName="Chang"
UnitsInStock="17" UnitsOnOrder="40"/><Products ProductName="Aniseed Syrup"
UnitsInStock="13" UnitsOnOrder="70"/></Suppliers><Suppliers CompanyName="New
Orleans Cajun Delights"><Products ProductName="Chef Anton's Cajun
Seasoning" UnitsInStock="53" UnitsOnOrder="0"/><Products ProductName="Chef
Anton's Gumbo Mix" UnitsInStock="0"
UnitsOnOrder="0"/></Suppliers><Suppliers CompanyName="Grandma Kelly's
Homestead"><Products ProductName="Grandma's Boysenberry Spread"
UnitsInStock="120" UnitsOnOrder="0"/><Products ProductName="Uncle Bob's
Organic Dried Pears" UnitsInStock="15" UnitsOnOrder="0"/><Products
ProductName="Northwoods Cranberry Sauce" UnitsInStock="6"
UnitsOnOrder="0"/></Suppliers...
how do i get the reslut in the XML format like this with the indents and new
lines(returns) after the xml objects instead of a single string?
<Suppliers CompanyName="Exotic Liquids">
<Products ProductName="Chai" UnitsInStock="39" UnitsOnOrder="0"/>
<Products ProductName="Chang" UnitsInStock="17" UnitsOnOrder="40"/>
<Products ProductName="Aniseed Syrup" UnitsInStock="13"
UnitsOnOrder="70"/>
</Suppliers>
<Suppliers CompanyName="New Orleans Cajun Delights">
<Products ProductName="Chef Anton's Cajun Seasoning"
UnitsInStock="53" UnitsOnOrder="0"/>
<Products ProductName="Chef Anton's Gumbo Mix" UnitsInStock="0"
UnitsOnOrder="0"/>
</Suppliers>...
I see all the examples like SQL parses out and format the xml in the results
window
Thx
Ron Sissons, DBA
Information Technology Services
Riverside County Office of Education
3939 Thirteenth Street, Riverside, CA 92502-0868
Telephone: (909) 826-6471; FAX: [909] 826-6451
|||Thank you very much!
I thought I was going crazy not understanding..in all the examples the wording is:
"results in the following format:
<Shippers>
<ShipperID>1</ShipperID>
<CompanyName>Speedy Express</CompanyName>
<Orders>
<OrderID>10249</OrderID>
..."
I thought I was missing something.
Ron Sissons, DBAInformation Technology ServicesRiverside County Office of Education3939 Thirteenth Street, Riverside, CA 92502-0868Telephone: (909) 826-6471; FAX: [909] 826-6451