问题描述:

I'm trying to get VBA to work with the Dell SOAP interface.

I think I've got about 99% of the way there as the request is successfully returning the data I asked for to cell A5.

I cannot however get the SystemModel node value.

Private Sub CommandButton1_Click()

'Set and instantiate our working objects

Dim sURL As String

Dim sEnv As String

Set xmlhtp = CreateObject("MSXML2.XMLHTTP")

Dim xmldoc As New MSXML2.DOMDocument60

sURL = "http://xserv.dell.com/services/assetservice.asmx?WSD"

' we create our SOAP envelope for submission to the Web Service

sEnv = "<?xml version=""1.0"" encoding=""utf-8""?>"

sEnv = sEnv & " <soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"

sEnv = sEnv & " <soap:Body>"

sEnv = sEnv & " <GetAssetInformation xmlns=""http://support.dell.com/WebServices/"">"

sEnv = sEnv & " <guid>12345678-1234-1234-1234-123456789012</guid>"

'Looks for SKU in active worksheet cell B3

sEnv = sEnv & " <applicationName>AssetService</applicationName>"

sEnv = sEnv & " <serviceTags>" & Worksheets("Sheet1").Range("A2").Value & "</serviceTags>"

sEnv = sEnv & " </GetAssetInformation>"

sEnv = sEnv & " </soap:Body>"

sEnv = sEnv & " </soap:Envelope>"

' Send SOAP Request

With xmlhtp

.Open "post", sURL, False

.setRequestHeader "Host", "xserv.dell.com"

.setRequestHeader "Content-Type", "text/xml; charset=utf-8"

.setRequestHeader "SOAPAction", "http://support.dell.com/WebServices/GetAssetInformation"

.setRequestHeader "Accept-encoding", "zip"

.send sEnv

xmldoc.LoadXML .responseText

xmldoc.setProperty "SelectionNamespaces", "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"

Set xmlElement = xmldoc.DocumentElement

Worksheets("Sheet1").Range("A5").Value = .responseText

Worksheets("Sheet1").Range("A3").Value = .SelectSingleNodes("//GetAssetInformationResult/Asset/AssetHeaderData/SystemModel").Text

MsgBox xmlElement.SelectNodes("Body/GetAssetInformationResult/Asset/AssetHeaderData/SystemModel").Text

End With

Dim xmltemplatestring As String

Set xmlRoot = xmldoc.DocumentElement

Set xmlChildren = xmlRoot.ChildNodes

For Each xmlTemplate In xmlChildren

xmltemplatestring = xmltemplatestring & xmlTemplate.nodeName

Next xmlTemplate

MsgBox xmltemplatestring

End Sub

These two lines do not work and generally gets runtime error 438

Worksheets("Sheet1").Range("A3").Value = .SelectSingleNodes("//GetAssetInformationResult/Asset/AssetHeaderData/SystemModel").Text

MsgBox xmlElement.SelectNodes("Body/GetAssetInformationResult/Asset/AssetHeaderData/SystemModel").Text

xmltemplatestring returns soap:Body

A5 contains the following text:

<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetAssetInformationResponse xmlns="http://support.dell.com/WebServices/"><GetAssetInformationResult><Asset><AssetHeaderData><ServiceTag>7Y5WR4J</ServiceTag><SystemID>poweredge-r510</SystemID><Buid>202</Buid><Region>Europe</Region><SystemType>PowerEdge</SystemType><SystemModel>PowerEdge R510</SystemModel><SystemShipDate>2010-05-30T19:00:00-05:00</SystemShipDate></AssetHeaderData><Entitlements><EntitlementData><ServiceLevelCode>NU</ServiceLevelCode><ServiceLevelDescription>ND ProSupport EndUser On-Site Extended with Dates</ServiceLevelDescription><Provider /><StartDate>2015-05-30T00:00:00</StartDate><EndDate>2016-05-31T00:00:00</EndDate><DaysLeft>316</DaysLeft><EntitlementType>Active</EntitlementType></EntitlementData><EntitlementData><ServiceLevelCode>NU</ServiceLevelCode><ServiceLevelDescription>ND ProSupport EndUser On-Site Extended with Dates</ServiceLevelDescription><Provider /><StartDate>2013-05-30T00:00:00</StartDate><EndDate>2015-05-31T00:00:00</EndDate><DaysLeft>0</DaysLeft><EntitlementType>Expired</EntitlementType></EntitlementData><EntitlementData><ServiceLevelCode>ND</ServiceLevelCode><ServiceLevelDescription>Next Business Day Onsite</ServiceLevelDescription><Provider /><StartDate>2010-05-30T00:00:00</StartDate><EndDate>2013-05-31T00:00:00</EndDate><DaysLeft>0</DaysLeft><EntitlementType>Expired</EntitlementType></EntitlementData><EntitlementData><ServiceLevelCode>4I</ServiceLevelCode><ServiceLevelDescription>4H ProSupport For IT Mission Extended with Dates</ServiceLevelDescription><Provider /><StartDate>2010-05-30T00:00:00</StartDate><EndDate>2013-05-31T00:00:00</EndDate><DaysLeft>0</DaysLeft><EntitlementType>Expired</EntitlementType></EntitlementData></Entitlements></Asset></GetAssetInformationResult></GetAssetInformationResponse></soap:Body></soap:Envelope>

Any help would be appreciated!

网友答案:

You could find it recursively through the child nodes

eg

Set xmlElement = xmldoc.DocumentElement
Set SystemModel = FindChild(xmlElement, "SystemModel")
Worksheets("Sheet1").Range("A3").Value = SystemModel.Text

Function FindChild(ByVal rootNode As Object, ByVal nodeName) As Object
Dim node As Object
Dim tempFind As Object

    For Each node In rootNode.ChildNodes
        If rootNode.nodeName = nodeName Then
            Set tempFind = node
        Else
            If node.nodeTypeString = "element" Then
                If tempFind Is Nothing Then
                    Set tempFind = FindChild(node, nodeName)
                End If
            End If
        End If
    Next node
    Set FindChild = tempFind
End Function

where FindChild(xmlElement, "SystemModel") passes an XML node and the nodeName to search

Also, there are multiple namespaces in the XML so you need to include them and also ensure they are prefixed to allow the correct path to be defined. This should work

Private Sub CommandButton1_Click()
Dim xmldoc As New MSXML2.DOMDocument60

    xmldoc.LoadXML .responseText
    xmldoc.setProperty "SelectionNamespaces", _
    "xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/' " & _
    "xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " & _
    "xmlns:xsd='http://www.w3.org/2001/XMLSchema' " & _
    "xmlns:mm='http://support.dell.com/WebServices/'"
    Set xmlElement = xmldoc.DocumentElement
    Set SystemModel = xmlElement.SelectSingleNode("//soap:Body/mm:GetAssetInformationResponse/mm:GetAssetInformationResult/mm:Asset/mm:AssetHeaderData/mm:SystemModel")
   Worksheets("Sheet1").Range("A3").Value = SystemModel.Text
   MsgBox SystemModel.Text

End Sub
网友答案:

SelectSingleNodes is not an existing method, and SelectNodes returns a nodelist, so you have to select a specific member of that list before trying to access its properties.

Try:

 xmlElement.SelectNodes( _
 "Body/GetAssetInformationResult/Asset/AssetHeaderData/SystemModel")(0).Text
相关阅读:
Top