The ADO persisted XML uses namespaces such as xmlns:rs="urn:schemas-microsoft-com:rowset" and xmlns:z="#RowsetSchema" for data rows. You have to use setProperty "SelectionNamespaces" before selecting the nodes using MSXML DOM, and then use namespaces prefixes in your XPath expression.
Consider the following example:
Dim objADOConn As New ADODB.Connection
Dim objADORS As New ADODB.Recordset
objADOConn.Open "PROVIDER=SQLOLEDB.1;SERVER=.;UID=sa;PWD=;DATABASE=Northwind;"
objADORS.Open "SELECT * FROM Customers", objADOConn
objADORS.Save "c:\NWCustomers.xml", adPersistXML
objADORS.Close
Set objADORS = Nothing
objADOConn.Close
Set objADOConn = Nothing
The above Visual Basic ADO code connects to Northwind sample SQL Server database and saves data from the Customers table into a XML file called as c:\NWCustomers.xml.
Dim objXMLDoc As New MSXML2.DOMDocument40
Dim aNode As IXMLDOMNode
objXMLDoc.async = False
objXMLDoc.validateOnParse = False
If objXMLDoc.Load("c:\NWCustomers.xml") Then
objXMLDoc.setProperty "SelectionNamespaces", _
"xmlns:rs='urn:schemas-microsoft-com:rowset' " & _
"xmlns:z='#RowsetSchema'"
Set aNode = objXMLDoc.selectSingleNode("/xml/rs:data/z:row[@CustomerID='BERGS']")
If aNode Is Nothing Then
MsgBox "Not found!"
Else
iAttribCount = aNode.Attributes.length
For iIndex = 0 To iAttribCount - 1
MsgBox aNode.Attributes.Item(iIndex).nodeName & " : " & _
aNode.Attributes.Item(iIndex).nodeTypedValue
Next
End If
Else
'use parseError for error reporting
End If
The above MSXML code loads the XML file created by the prior VB ADO code. Note how the setProperty "SelectionNamespaces" is set and the namespaces prefixes used in the XPath expression. The above sample VB code searches for customer with ID equals BERGS; if found, displays all the attributes on that customer node.
|
