[RESOLVED] Extracting data from XML

[dlscott56]

I was trying to use a MSXML2.DOMDocument to extract data I need from an XML response to a SOAP call. The return data looks something like

<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<ExecuteDataSourceResponse xmlns="http://www.plexus-online.com/DataSource">
<ExecuteDataSourceResult>
<StatusNo>0</StatusNo>
<Error>false</Error>
<ErrorNo>0</ErrorNo>
<Message>Success</Message>
<InstanceNo>219</InstanceNo>
<DataSourceKey>65299</DataSourceKey>
<QuarantinedForDevelopment xsi:nil="true" />
<LastPrimaryDeployment xsi:nil="true" />
<LastTestDeployment xsi:nil="true" />
<Version xsi:nil="true" />
<DataSourceName>sproc283025_Recipe_Dip_Spin_Get</DataSourceName>
<ResultSets>
<ResultSet>
<RowCount>2</RowCount>
<Rows>
<Row>
<Columns>
<Column>
<Value>DIP/SPIN LINE 21</Value>
<Name>Str_Production_Line</Name>
</Column>
<Column>
<Value>0</Value>
<Name>Int_Angle_Of_Tilt</Name>
</Column>
<Column>
<Value>DGZ 0000 - 1</Value>
<Name>Intpk_Part_Step_Ds_Id</Name>
</Column>
<Column>
<Value>6828526</Value>
<Name>Intpk_Part_Step_Id</Name>
</Column>
<Column>
</Columns>
</Row>
<Row>
<Columns>
<Column>
<Value>DIP/SPIN LINE 22</Value>
<Name>Str_Production_Line</Name>
</Column>
<Column>
<Value>20</Value>
<Name>Int_Angle_Of_Tilt</Name>
</Column>
<Column>
<Value>DGZ 0000 - 1</Value>
<Name>Intpk_Part_Step_Ds_Id</Name>
</Column>
<Column>
<Value>6828528</Value>
<Name>Intpk_Part_Step_Id</Name>
</Column>
</Columns>
</Row>
</Rows>
</ResultSet>
</ResultSets>
</ExecuteDataSourceResult>
</ExecuteDataSourceResponse>
</soap:Body>
</soap:Envelope>


I'm trying to access specific values but am not sure how to specify a particular row. My original attempt was -

Dim doc As New MSXML2.DOMDocument
    
   Dim success As Boolean

   success = doc.loadXML(txtResponse.Text)
   
   If success = False Then
      MsgBox doc.parseError.reason
   Else
        MsgBox "Str_Production_Line = " & doc.selectSingleNode("//Column[Name='Str_Production_Line']/Value").nodeTypedValue
   End If

This returns the first value but I don't know how to modify it to loop through each row and get the value.

[LaVolpe]

Your attempt was valid, but the document uses namespaces and therefore the XPath needs to include them too.

two odd things with your sample XML

1. Line 39 does not have a matched closing tag, i.e., <Column> without a </Column>
2. The document lists 3 namespaces in the 1st element, those xmlns attributes

However, the <ExecuteDataSourceResponse> element uses a totally different namespace, not one of those three. But because all the nodes that follow are children of that element, they all have that same namespace unless they include their own & they don't.

With your code, you add the needed namespace or parse out all namespaces (not ideal). To add desired namespaces:

Dim ns As String
    ns = "xmlns:plexus='http://www.plexus-online.com/DataSource'"
    doc.setProperty "SelectionNamespaces", ns: ns = "plexus:"

The bolded part above can be any nickname you want and you reference the namespace in the XPath queries with that nickname. All elements are prefixed with it, not attributes.

MsgBox "Str_Production_Line = " & doc.selectSingleNode("//" & ns & "Column[" & _
             ns & "Name='Str_Production_Line']/" & ns & "Value").nodeTypedValue

[dilettante]

Use a SOAP client library. Trying to fashion one from stone knives and bearskins is a lot of work with little payoff, high risk of errors and data loss or corruption, and high maintenance costs.

Or even better move to a stable REST API if the provider offers one. SOAP is all but dead.

[wqweto]

This returns the first value but I don't know how to modify it to loop through each row and get the value.

This selectNodes like this

' replace `Else` part w/ this
        Dim oNode As IXMLDOMNode
        For Each oNode In doc.selectNodes("//Column[Name='Str_Production_Line']/Value")
            MsgBox "Str_Production_Line = " & oNode.nodeTypedValue
        Next

cheers,
</wqw>

[dlscott56]

Thanks LaVolpe. The missing </Column> was my fault as I cut quite a few of the values from each row to shorten the post. So, I've changed the code as you've suggested to specify the namespace containing all of the nodes I'm interested in.

In this example there are 2 rows of data and my current code pulls up the Str_Production_Line value from the first row. Now I would like to know how to loop through the rows included in <ResultSet></ResultSet> and extract values, like Str_Production_Line, from each row.

[dlscott56]

Thanks dilettante. I will look at the SOAP client library. I have no choice on moving to REST API in this project.

[dlscott56]

Thanks wqweto.

[wqweto]

I have no choice on moving to REST API in this project.

When dealing w/ SOAP services I'm applying a hybrid REST mode approach by using my mdJson.bas to convert XML to JSON and deal w/ it internally.

For instance your service reponse can be converted like this

Option Explicit
 
Private Declare Function MultiByteToWideChar Lib "kernel32" (ByVal CodePage As Long, ByVal dwFlags As Long, lpMultiByteStr As Any, ByVal cchMultiByte As Long, ByVal lpWideCharStr As Long, ByVal cchWideChar As Long) As Long
 
Private Sub Form_Load()
    Dim oJson           As Object
 
    Set oJson = JsonFromXmlDocument(FromUtf8Array(ReadBinaryFile("d:\temp\aaa.xml")))
    Debug.Print JsonDump(oJson)
End Sub
 
Public Function ReadBinaryFile(sFile As String) As Byte()
    Dim baBuffer()      As Byte
    Dim nFile           As Integer
    
    On Error GoTo EH
    baBuffer = vbNullString
    nFile = FreeFile
    Open sFile For Binary Access Read Shared As nFile
    If LOF(nFile) > 0 Then
        ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
        Get nFile, , baBuffer
    End If
    Close nFile
    ReadBinaryFile = baBuffer
    Exit Function
EH:
    Close nFile
End Function
 
Public Function FromUtf8Array(baText() As Byte) As String
    Const CP_UTF8       As Long = 65001
    Dim lSize           As Long
    
    If UBound(baText) >= 0 Then
        FromUtf8Array = String$(2 * UBound(baText), 0)
        lSize = MultiByteToWideChar(CP_UTF8, 0, baText(0), UBound(baText) + 1, StrPtr(FromUtf8Array), Len(FromUtf8Array))
        FromUtf8Array = Left$(FromUtf8Array, lSize)
    End If
End Function

... to a more convenient (for me) JSON object like this

{
    "@xmlns:soap": "http://www.w3.org/2003/05/soap-envelope",
    "@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
    "@xmlns:xsd": "http://www.w3.org/2001/XMLSchema",
    "soap:Body": {
        "ExecuteDataSourceResponse": {
            "@xmlns": "http://www.plexus-online.com/DataSource",
            "ExecuteDataSourceResult": {
                "StatusNo": 0,
                "Error": "false",
                "ErrorNo": 0,
                "Message": "Success",
                "InstanceNo": 219,
                "DataSourceKey": 65299,
                "QuarantinedForDevelopment": { "@xsi:nil": "true" },
                "LastPrimaryDeployment": { "@xsi:nil": "true" },
                "LastTestDeployment": { "@xsi:nil": "true" },
                "Version": { "@xsi:nil": "true" },
                "DataSourceName": "sproc283025_Recipe_Dip_Spin_Get",
                "ResultSets": {
                    "ResultSet": {
                        "RowCount": 2,
                        "Rows": {
                            "Row": [
                                {
                                    "Columns": {
                                        "Column": [
                                            { "Value": "DIP/SPIN LINE 21", "Name": "Str_Production_Line" },
                                            { "Value": 0, "Name": "Int_Angle_Of_Tilt" },
                                            { "Value": "DGZ 0000 - 1", "Name": "Intpk_Part_Step_Ds_Id" },
                                            { "Value": 6828526, "Name": "Intpk_Part_Step_Id" }
                                        ]
                                    }
                                },
                                {
                                    "Columns": {
                                        "Column": [
                                            { "Value": "DIP/SPIN LINE 22", "Name": "Str_Production_Line" },
                                            { "Value": 20, "Name": "Int_Angle_Of_Tilt" },
                                            { "Value": "DGZ 0000 - 1", "Name": "Intpk_Part_Step_Ds_Id" },
                                            { "Value": 6828528, "Name": "Intpk_Part_Step_Id" }
                                        ]
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        }
    }
}

From this point on I just use JsonItem getter/setter on oJson object to delve into the data and convert it from/to my particular app plain-old-data-objects.

cheers,
</wqw>

[dlscott56]

This selectNodes like this

' replace `Else` part w/ this
        Dim oNode As IXMLDOMNode
        For Each oNode In doc.selectNodes("//Column[Name='Str_Production_Line']/Value")
            MsgBox "Str_Production_Line = " & oNode.nodeTypedValue
        Next

cheers,
</wqw>

Now I'm curious to know if I can go through each row, loop through the name/value pairs in the row and examine each one individually?

[dlscott56]

From this point on I just use JsonItem getter/setter on oJson object to delve into the data and convert it from/to my particular app plain-old-data-objects.

cheers,
</wqw>

Well I'm not familiar with JSON either. But am willing to learn if it makes things easier. I'll do some reading. I'm making just a few datasource calls using SOAP as defined by the provider. It's just passing stored procedure calls and parameters to them and the data returned is similar format to what I posted. Then using the data in my code to set machine parameters.

[wqweto]

Now I'm curious to know if I can go through each row, loop through the name/value pairs in the row and examine each one individually?

Try this:

'-- in Else
        Dim oRow As IXMLDOMNode
        Dim oColumn As IXMLDOMNode
        Dim sRow As String
        For Each oRow In doc.selectNodes("//Rows/Row")
            sRow = vbNullString
            For Each oColumn In oRow.selectNodes("Columns/Column")
                sRow = sRow & oColumn.selectSingleNode("Name").Text & "=" & oColumn.selectSingleNode("Value").Text & vbCrLf
            Next
            MsgBox "sRow=" & sRow, vbExclamation
        Next

cheers,
</wqw>

[dlscott56]

Try this:

'-- in Else
        Dim oRow As IXMLDOMNode
        Dim oColumn As IXMLDOMNode
        Dim sRow As String
        For Each oRow In doc.selectNodes("//Rows/Row")
            sRow = vbNullString
            For Each oColumn In oRow.selectNodes("Columns/Column")
                sRow = sRow & oColumn.selectSingleNode("Name").Text & "=" & oColumn.selectSingleNode("Value").Text & vbCrLf
            Next
            MsgBox "sRow=" & sRow, vbExclamation
        Next

cheers,
</wqw>

Perfect! Thanks to all of you for your help with this!