XML to DataSet

[LochDhu]

Hi all,

I have a SQL2000 table where one field is text and contains an XML document.

What I need to do is query the row and take the contents of the XML field
and create a DataSet from it. That DataSet will then be bound to a control.

Updates can be made and then I need to convert the Dataset back to XML and
update the field in the DB. Somehow I am stumbling on this and wasting
time - I've looked at XMLDataDocument and StreamReader but I can't seem to
load the string field value (XML contents) into one of these.

Does anyone have any suggestions or direction for me?

Thanks In Advance,

Scott

 

[JohnYingling]

Dim dsXmlTr As System.Xml.XmlTextReader
Try
dsXmlTr = New System.Xml.XmlTextReader _
(New IO.StringReader(sXML))
dsRsp.ReadXml(dsXmlTr, XmlReadMode.IgnoreSchema)
Catch ex As Exception
Dim myex As New System.ApplicationException( _
"HandleAceResponse: " & dsRsp.DataSetName & ": Response=" & sXML, ex)
ei.FailureCode = RPFailureCode.DataSetReadXmlError
ei.ErrorCode = "XX"
ei.ErrorMessage = myex.ToString
LogAbendMessage(ei.ErrorMessage)
Return ei
Finally
Try
' Close XMLTextReader and underlying StreamReader
dsXmlTr.Close()
Catch
End Try

End Try

http://www.VBCompare.com

Compare Code

 

[LochDhu]

thanks for the tip, but I still can't egt it to work:

Here is the xml that is stored as text in the db:

<?xml version="1.0" standalone="yes"?>
<dsLayers>
<layers>
<layername>Conduits</layername>
<layerOrder>0</layerOrder>
</layers>
<layers>
<layername>Valves</layername>
<layerOrder>1</layerOrder>
</layers>
</dsLayers>

Here is snippet of code I have tried:

sqlDA.Fill(sqlDS)'fill dataset from sql 2000
sqlCon.Dispose()

'new dataset to hold xml-based table
Dim dsRsp As New DataSet("myXML")

'Put the text(xml) contents into string var
Dim sXML As String = sqlDS.Tables(0).Rows(0).Item(1)

Dim dsXmlTr As System.Xml.XmlTextReader
   dsXmlTr = New System.Xml.XmlTextReader _
                (New IO.StringReader(sXML))

'no errors - but dsXmlTr.Value = ""

dsRsp.ReadXml(dsXmlTr, XmlReadMode.IgnoreSchema)

'No error - but dsRsp.Tables.Count = 0

am i doing something wrong?

Scott

 

[JohnYingling]

What is in sXML after
Dim sXML As String = sqlDS.Tables(0).Rows(0).Item(1)

http://www.VBCompare.com

Compare Code

 

[LochDhu]

Thanks for your patience:

sXML contains the following XML content:

<?xml version="1.0" standalone="yes"?>
<dsLayers>
<layers>
<layername>Conduits</layername>
<layerOrder>0</layerOrder>
</layers>
<layers>
<layername>Valves</layername>
<layerOrder>1</layerOrder>
</layers>
</dsLayers>


Scott

 

[LochDhu]

Here is the solution - thanks for oyur suggestions:

Dim s As String = sqlDS.Tables(0).Rows(0).Item(1)
Dim reader As New System.IO.StringReader(s)
   dsXML.ReadXmlSchema("C:\dsLayers.xsd")
   dsXML.ReadXml(reader)

Scott