XML From Stored Proc

[Jay]

In VB.NET (Studio 2005) I need to loop thru a result set returned from a
stored proc and generate XML for each row. E.g. exex procWhatever returns:

custno, lname, fname, address
2, Doe, John, 123 4th St.
3, Smith, Bill, 567 8th St.
4, Grant, Terry, 333 Wherever St.

For each on of those rows I need to generate (string):

<Customer>
<custno>2</custno>
<lname>Doe</lname>
<fname>John</fname>
<address>123 4th st.</address>
</customer>

....


Any advice, tutorials, etc. would be greatly appreciated. Thank you very
much.

 

[Cowboy \(Gregory A. Beamer\)]

What database? If SQL Server, you have the option of outputting as XML,
using FOR XML.

If not, you will have to do it yourself. One of the easiest means is taking
the DataSet XML (GetXml()) and transforming it with XSLT to the format you
would like.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************

 

[Jay]

Thanks for the prompt response. The database is SQL 2005 but when I return
the results using FOR XML AUTO, ELEMENTS I receive all customers in one xml
document like so:


<Customer>
<custno>2</custno>
<lname>Doe</lname>
<fname>John</fname>
<address>123 4th st.</address>
</customer>
<Customer>
<custno>3</custno>
<lname>Smith</lname>
<fname>Terry</fname>
<address>456 7th st..</address>
</customer>
....
....

I can return this to VB.NET but I need to loop thru and use each row
individually, not as an entire document.

So I figure if I remove for xml auto, elements from the query/proc I can
genetate the xml for each row individually within vb.net... unless here is
an easier way to do it with the xml results returned from sql server.

Any thoughts, recommendations? Thanks a lot.

 

[Cor Ligthert [MVP]]

Jay,

XML is only a way of how to describe data. Which can be used in many ways.

If you use it inside ADONET than you mostly using a datatable (which can be
wrapped inside a dataset)

For that you need only a simple Select command describing what you want and
than
A SQLConnection
A DataTable
A SQLDataAdapter
the SQL Select command

If you use than the dataadapter as
da.Fill(myTable) than you have it.

Inside and around the DataSet/DataTable are and endless amount of methods by
instance the DataSet/DataTable writeXML and ReadXml.

I hope this helps,

Cor

 

[Jeff Dillon]

Using FOR XML EXPLICIT, you can create most any XML structure you want.