Solved

Consuming XML from a SQL server 2008 stored proc

Posted on 2013-10-22
2
278 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
 
LVL 1

Author Closing Comment

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

/Aly
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

813 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

17 Experts available now in Live!

Get 1:1 Help Now