[RESOLVED] Extracting data from XML

[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>