FOR XML EXPLICIT

Hello.
I'm trying to list two columns and count one from it.
Here's my code:
SELECT Caption0 as 'Operating System', CSDVersion0 , COUNT(CSDVersion0) as
'Number'
FROM v_GS_OPERATING_SYSTEM
GROUP BY Caption0, CSDVersion0
ORDER BY Caption0, CSDVersion0
FOR XML EXPLICIT
I have a message, that I FOR XML EXPLICIT requiers the first column to
hold positive integers that represent XML tags IDs.
I know what it means, but i'm nOOb. What i must do? I think I looked by
google everywhere...
Legrooch
You may want to look at the articles linked from
http://blogs.msdn.com/mrys/archive/2...09/151523.aspx
While they talk about the new PATH mode, they contain several FOR XML
EXPLICIT examples.
Feel free to come back if that is not enough information.
Best regards
Michael
"Legrooch" <leszek.gruszka@.kana.com.pl> wrote in message
news:opsavdy3l2732xkw@.k3-xp-lg.k3.win...
> Hello.
> I'm trying to list two columns and count one from it.
> Here's my code:
> SELECT Caption0 as 'Operating System', CSDVersion0 , COUNT(CSDVersion0) as
> 'Number'
> FROM v_GS_OPERATING_SYSTEM
> GROUP BY Caption0, CSDVersion0
> ORDER BY Caption0, CSDVersion0
> FOR XML EXPLICIT
> I have a message, that I FOR XML EXPLICIT requiers the first column to
> hold positive integers that represent XML tags IDs.
> I know what it means, but i'm nOOb. What i must do? I think I looked by
> google everywhere...
> --
> Legrooch

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