Wrox Programmer Forums
Go Back   Wrox Programmer Forums > XML > XML
| Search | Today's Posts | Mark Forums Read
XML General XML discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XML section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 10th, 2005, 09:08 AM
Authorized User
 
Join Date: Nov 2004
Location: , , .
Posts: 57
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem with ReadXML

I am attempting to read the data from an XML document into a SQL Server database. The XML document has multiple root elements which contain different rows of information. When I use ReadXML it gives me an exeption stating "Multiple Root Elements". Here is my code:

Dim objAdapter As SqlDataAdapter
        Dim objDataRow, objDBRow As DataRow
        Dim objDSXML As New DataSet
        Dim objDSDBTable As New DataSet("tbl_XML_Test")
        Dim ObjCmdBuilder As SqlCommandBuilder

        objDSXML.ReadXml("C:\live.xml")
        objSQLConn.Open()
        objAdapter = New SqlDataAdapter("SELECT sSym, sLast, sChg, sOpenInt, sClose, sVolume, sDesc FROM tbl_XML_Test WHERE 1 = 2 ", objSQLConn)
        objAdapter.Fill(objDSDBTable, "tbl_XML_Test")
        For Each objDataRow In objDSXML.Tables(0).Rows

            With objDSDBTable.Tables(0)

                objDBRow = .NewRow()

                objDBRow(0) = objDataRow(0)
                objDBRow(1) = objDataRow(1)
                objDBRow(2) = objDataRow(2)
                objDBRow(3) = objDataRow(3)
                objDBRow(4) = objDataRow(4)
                objDBRow(5) = objDataRow(5)
                objDBRow(6) = objDataRow(6)

                .Rows.Add(objDBRow)

            End With

            ObjCmdBuilder = New SqlCommandBuilder(objAdapter)
            objAdapter.Update(objDSDBTable, "tbl_XML_Test")

        Next
        objSQLConn.Close()

Here is part of the XML document which has the same repeating structure, just different data:

 <ALLQUOTEREQUEST_ROW>
  <LAST>90.000000</LAST>
  <CHG>0</CHG>
  <OPENINT>0</OPENINT>
  <CLOSE>90.000000</CLOSE>
  <VOLUME>0</VOLUME>
  <DESC>Live Cattle</DESC>
  <SYM>LC5K</SYM>
  </ALLQUOTEREQUEST_ROW>
 <ALLQUOTEREQUEST_ROW>
  <LAST>85.400000</LAST>
  <CHG>0.35</CHG>
  <OPENINT>67206</OPENINT>
  <CLOSE>85.400000</CLOSE>
  <VOLUME>16495</VOLUME>
  <DESC>Live Cattle</DESC>
  <SYM>LC5M</SYM>
  </ALLQUOTEREQUEST_ROW>

Any code examples would be appreciated. Thanks for the help!!!

  #2 (permalink)  
Old May 11th, 2005, 09:54 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

Firstly you need to modify the SQL query to produce valid XML with an all encompassing root tag. An easy way to do this if the machine has SqlXml installed is to use the SqlXmlCommand from the Microsoft.Data namespace.
For merging two datatables why not use the Dataset.Merge method?


--

Joe (Microsoft MVP - XML)


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can you attach xsl to xml with ReadXML for VB.NET? boondocksaint20 XML 3 April 28th, 2006 03:19 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.