PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Extracting data from XML-VBForums
Results 1 to 12 of 12

Thread: [RESOLVED] Extracting data from XML

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    146

    Resolved [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.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  2. #2
    VB-aholic & Lovin' It LaVolpe's Avatar
    Join Date
    Oct 2007
    Location
    Beside Waldo
    Posts
    18,234

    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
    Last edited by LaVolpe; Jun 10th, 2019 at 07:31 PM.
    Insomnia is just a byproduct of, "It can't be done"

    Classics Enthusiast? Here's my 1969 Mustang Mach I Fastback. Her sister '67 Coupe has been adopted

    Newbie? Novice? Bored? Spend a few minutes browsing the FAQ section of the forum.
    Read the HitchHiker's Guide to Getting Help on the Forums.
    Here is the list of TAGs you can use to format your posts
    Here are VB6 Help Files online


    {Alpha Image Control} {Memory Leak FAQ} {Unicode Open/Save Dialog} {Resource Image Viewer/Extractor}
    {VB and DPI Tutorial} {Manifest Creator} {UserControl Button Template} {stdPicture Render Usage}

  3. #3
    PowerPoster
    Join Date
    Feb 2006
    Posts
    20,313

    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.

  4. #4
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,476

    Re: Extracting data from XML

    Quote Originally Posted by dlscott56 View Post
    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:
    1. ' replace `Else` part w/ this
    2.         Dim oNode As IXMLDOMNode
    3.         For Each oNode In doc.selectNodes("//Column[Name='Str_Production_Line']/Value")
    4.             MsgBox "Str_Production_Line = " & oNode.nodeTypedValue
    5.         Next
    cheers,
    </wqw>

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    146

    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.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    146

    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.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    146

    Re: Extracting data from XML

    Thanks wqweto.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  8. #8
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,476

    Re: Extracting data from XML

    Quote Originally Posted by dlscott56 View Post
    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:
    1. Option Explicit
    2.  
    3. 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
    4.  
    5. Private Sub Form_Load()
    6.     Dim oJson           As Object
    7.  
    8.     Set oJson = JsonFromXmlDocument(FromUtf8Array(ReadBinaryFile("d:\temp\aaa.xml")))
    9.     Debug.Print JsonDump(oJson)
    10. End Sub
    11.  
    12. Public Function ReadBinaryFile(sFile As String) As Byte()
    13.     Dim baBuffer()      As Byte
    14.     Dim nFile           As Integer
    15.    
    16.     On Error GoTo EH
    17.     baBuffer = vbNullString
    18.     nFile = FreeFile
    19.     Open sFile For Binary Access Read Shared As nFile
    20.     If LOF(nFile) > 0 Then
    21.         ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
    22.         Get nFile, , baBuffer
    23.     End If
    24.     Close nFile
    25.     ReadBinaryFile = baBuffer
    26.     Exit Function
    27. EH:
    28.     Close nFile
    29. End Function
    30.  
    31. Public Function FromUtf8Array(baText() As Byte) As String
    32.     Const CP_UTF8       As Long = 65001
    33.     Dim lSize           As Long
    34.    
    35.     If UBound(baText) >= 0 Then
    36.         FromUtf8Array = String$(2 * UBound(baText), 0)
    37.         lSize = MultiByteToWideChar(CP_UTF8, 0, baText(0), UBound(baText) + 1, StrPtr(FromUtf8Array), Len(FromUtf8Array))
    38.         FromUtf8Array = Left$(FromUtf8Array, lSize)
    39.     End If
    40. 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>

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    146

    Re: Extracting data from XML

    Quote Originally Posted by wqweto View Post
    This selectNodes like this

    thinBasic Code:
    1. ' replace `Else` part w/ this
    2.         Dim oNode As IXMLDOMNode
    3.         For Each oNode In doc.selectNodes("//Column[Name='Str_Production_Line']/Value")
    4.             MsgBox "Str_Production_Line = " & oNode.nodeTypedValue
    5.         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?
    Last edited by dlscott56; Jun 11th, 2019 at 08:23 AM.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    146

    Re: Extracting data from XML

    Quote Originally Posted by wqweto View Post
    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.
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

  11. #11
    Frenzied Member wqweto's Avatar
    Join Date
    May 2011
    Posts
    1,476

    Re: Extracting data from XML

    Quote Originally Posted by dlscott56 View Post
    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:
    1. '-- in Else
    2.         Dim oRow As IXMLDOMNode
    3.         Dim oColumn As IXMLDOMNode
    4.         Dim sRow As String
    5.         For Each oRow In doc.selectNodes("//Rows/Row")
    6.             sRow = vbNullString
    7.             For Each oColumn In oRow.selectNodes("Columns/Column")
    8.                 sRow = sRow & oColumn.selectSingleNode("Name").Text & "=" & oColumn.selectSingleNode("Value").Text & vbCrLf
    9.             Next
    10.             MsgBox "sRow=" & sRow, vbExclamation
    11.         Next
    cheers,
    </wqw>

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Mar 2010
    Location
    Southeast Michigan
    Posts
    146

    Re: Extracting data from XML

    Quote Originally Posted by wqweto View Post
    Try this:

    thinBasic Code:
    1. '-- in Else
    2.         Dim oRow As IXMLDOMNode
    3.         Dim oColumn As IXMLDOMNode
    4.         Dim sRow As String
    5.         For Each oRow In doc.selectNodes("//Rows/Row")
    6.             sRow = vbNullString
    7.             For Each oColumn In oRow.selectNodes("Columns/Column")
    8.                 sRow = sRow & oColumn.selectSingleNode("Name").Text & "=" & oColumn.selectSingleNode("Value").Text & vbCrLf
    9.             Next
    10.             MsgBox "sRow=" & sRow, vbExclamation
    11.         Next
    cheers,
    </wqw>
    Perfect! Thanks to all of you for your help with this!
    Dave

    Helpful information I've found here so far : The Definitive "Passing Data Between Forms" : Restrict TextBox to only certain characters, numeric or symbolic :
    .NET Regex Syntax (scripting) : .NET Regex Language Element : .NET Regex Class : Regular-Expressions.info
    Stuff I've learned here so far : Bing and Google are your friend. Trying to help others solve their problems is a great learning experience

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width