[RESOLVED] Extracting data from XML
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 -
Code:
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.
Re: Extracting data from XML
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:
Code:
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.
Code:
MsgBox "Str_Production_Line = " & doc.selectSingleNode("//" & ns & "Column[" & _
ns & "Name='Str_Production_Line']/" & ns & "Value").nodeTypedValue
Re: Extracting data from XML
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.
Re: Extracting data from XML
Quote:
Originally Posted by
dlscott56
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
thinBasic Code:
' 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>
Re: Extracting data from XML
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.
Re: Extracting data from XML
Thanks dilettante. I will look at the SOAP client library. I have no choice on moving to REST API in this project.
Re: Extracting data from XML
Re: Extracting data from XML
Quote:
Originally Posted by
dlscott56
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
thinBasic Code:
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
Code:
{
"@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>
Re: Extracting data from XML
Quote:
Originally Posted by
wqweto
This
selectNodes like this
thinBasic Code:
' 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?
Re: Extracting data from XML
Quote:
Originally Posted by
wqweto
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.
Re: Extracting data from XML
Quote:
Originally Posted by
dlscott56
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:
thinBasic Code:
'-- 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>
Re: Extracting data from XML
Quote:
Originally Posted by
wqweto
Try this:
thinBasic Code:
'-- 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!
