Solved

Consuming XML from a SQL server 2008 stored proc

Posted on 2013-10-22
2
274 Views
Last Modified: 2013-10-23
I am looking for a good guide or article that demonstrates how to consume XML from a SQL server 2008 stored proceedure

A simplified version of the stored proc
SELECT    UserName
		,Forename
		,Surname
		,Email 
FROM dbo.UserDetails 
FOR XML RAW, ELEMENTS;

Open in new window


and a samle of the XML returned

<row>
  <UserName>preinvoiceca</UserName>
  <Forename>Detached Invoice</Forename>
  <Surname>Processor CA</Surname>
  <Email>preinvoiceca@trailers.com</Email>
</row>
<row>
  <UserName>preinvoiceusa</UserName>
  <Forename>Detached Invoice</Forename>
  <Surname>Processor USA</Surname>
  <Email>preinvoiceusa@trailers.com</Email>
</row>

Open in new window

0
Comment
Question by:Alyanto
2 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39591562
Some examples with different techniques are posted in following link

http://developer.yahoo.com/dotnet/howto-xml_vb.html

You can also convert it to a dataset

http://www.nullskull.com/faq/1889/convert-an-xml-string-into-a-dataset.aspx


Public Shared Function XMLToDataSet(ByVal xmlStr As String, ByVal schemaFile As String) As DataSet
        'Convert the XML to a dataset
        Dim sr As New StringReader(xmlStr)

        'Convert xmlData to a Dataset
        Dim ds As New DataSet

        If schemaFile = String.Empty Then
            ds.ReadXml(sr, XmlReadMode.InferSchema)
        Else
            ds.ReadXmlSchema(schemaFile)
            ds.ReadXml(sr, XmlReadMode.ReadSchema)
        End If

        For Each relation As DataRelation In ds.Relations
            For Each c As DataColumn In relation.ParentColumns
                If Not relation.ChildTable.Columns.Contains(c.ColumnName) Then
                    relation.ChildTable.Columns.Add(c)
                End If
                For Each dr As DataRow In relation.ChildTable.Rows
                    dr(c.ColumnName) = dr.GetParentRow(relation)(c.ColumnName)
                Next
            Next
        Next

        Return ds
    End Function

Open in new window

0
 

Author Closing Comment

by:Alyanto
ID: 39593348
Spot on, and timely too :)

/Aly
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now