Retrieve data from xml document

10 Mar 2006 8:15 AM

Philip Wagenaar

I have an xml document I want to extract information from and I am having a
hard time achieving this.

I want to extract data from the field elements. I want to know the name and
the contents in the CDATA.

So in the doc posted below I want to know:

name = dossiernr and data = test
name = naam and data = test

My xml doc:

<?xml version="1.0" encoding="UTF-8" ?>
- <RESPONSE XMLID="dd8433dd-356a-4cad-9d9d-0dacdd5fcc0e">
<SUCCESS REQUESTID="0" COMMAND="LOGIN" />
- <SUCCESS REQUESTID="1" COMMAND="DOCUMENT">
- <DOCUMENT ID="$(#STANDARD)\TROOST,00000008,002" FIELDCOUNT="3"
CREATION="1139911549" EDITED="1141917788" ARCHIVED="1">
- <FIELD ID="10" NAME="DOSSIERNR" TYPE="STRING" USE="USER" CODE="ANSI"
ATTRIB="FieldID=1001" SEGMID="0">
- <DATA>
- <![CDATA[ test
]]>
</DATA>
</FIELD>
- <FIELD ID="11" NAME="NAAM" TYPE="STRING" USE="USER" CODE="ANSI"
ATTRIB="FieldID=1002" SEGMID="1">
- <DATA>
- <![CDATA[ test
]]>
</DATA>
</FIELD>
</DOCUMENT>
</SUCCESS>
</RESPONSE>

10 Mar 2006 9:21 AM

Luke Zhang [MSFT]

Hello,

An easy way is to load this XML document in a dataset, and access the
values via datatable's row and column, for example:

Dim ds As New DataSet
ds.ReadXml("c:\test.xml")

     Dim dr As DataRow

        For Each dr In ds.Tables(3).Rows
            MsgBox("name = " + dr("name") + " and data =" + dr("data"))
        Next

After read the XML document in a dataset, there will be four tables:

RESPONSE
SUCCESS
DOCUMENT
FIELD

The value we needs is in the last one Tables(3): FIELD

Hope this help,

Luke Zhang
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

10 Mar 2006 10:01 AM

Philip Wagenaar

Sure helps a lot!

Can I also load an XmlDocument into the dataset? Well, not directly I know,
but what is the best way?

Show quoteHide quote

"Luke Zhang [MSFT]" wrote:

> Hello,
>
> An easy way is to load this XML document in a dataset, and access the
> values via datatable's row and column, for example:
>
> Dim ds As New DataSet
>   ds.ReadXml("c:\test.xml")
>
>
>      Dim dr As DataRow
>
>         For Each dr In ds.Tables(3).Rows
>             MsgBox("name = " + dr("name") + " and data =" + dr("data"))
>         Next
>
> After read the XML document in a dataset, there will be four tables:
>
> RESPONSE
> SUCCESS
> DOCUMENT
> FIELD
>
> The value we needs is in the last one Tables(3): FIELD
>
> Hope this help,
>
> Luke Zhang
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>

10 Mar 2006 10:26 AM

Philip Wagenaar

I get the following error:

Message="Column name 'REFERENCE' is defined for different mapping types."

my code:

Dim XmlString As String
        XmlString = "<?xml version=""1.0"" encoding=""UTF-8"" ?>" & vbCrLf

        Dim reader As New XmlNodeReader(xmldoc)
        Dim ds As New DataSet
        ds.ReadXml(reader)
        Dim dr As DataRow
        For Each dr In ds.Tables(3).Rows
            XmlString &= "<INDEX NAME=""" & dr("name") & """>" & dr("data")
& "</INDEX>"
        Next

        Dim IndexXml As New XmlDocument
        IndexXml.InnerXml = XmlString
        Return IndexXml

Show quoteHide quote

"Luke Zhang [MSFT]" wrote:

> Hello,
>
> An easy way is to load this XML document in a dataset, and access the
> values via datatable's row and column, for example:
>
> Dim ds As New DataSet
>   ds.ReadXml("c:\test.xml")
>
>
>      Dim dr As DataRow
>
>         For Each dr In ds.Tables(3).Rows
>             MsgBox("name = " + dr("name") + " and data =" + dr("data"))
>         Next
>
> After read the XML document in a dataset, there will be four tables:
>
> RESPONSE
> SUCCESS
> DOCUMENT
> FIELD
>
> The value we needs is in the last one Tables(3): FIELD
>
> Hope this help,
>
> Luke Zhang
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>

13 Mar 2006 5:31 AM

Luke Zhang [MSFT]

I didn't see 'REFERENCE' element in your original XML data, did you load a
different xml file?

Luke Zhang
(This posting is provided "AS IS", with no warranties, and confers no
rights.)