Solved

Consuming XML from a SQL server 2008 stored proc

Posted on 2013-10-22
2
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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