執行包含 XPath 查詢的範本 (SQLXMLOLEDB 提供者)

此範例顯示如何使用下列 SQLXMLOLEDB 提供者專屬的屬性：

• ClientSideXML

• Base Path

• Mapping Schema

在此範例 ADO 應用程式中，由 XPath 查詢 (根) 組成的 XML 範本會根據＜執行 XPath 查詢 (SQLXMLOLEDB 提供者)＞中描述之 XSD 對應結構描述 (MySchema.xml) 來指定。

Mapping Schema 屬性會根據執行的 XPath 查詢提供 XSD 對應結構描述。Base Path 屬性會提供對應結構描述的檔案路徑。

ClientSideXML 屬性會設定為 True。因此，XML 文件會在用戶端上產生。

在應用程式中，會直接指定 XPath 查詢。因此，必須包含 Dialect {5d531cb2-e6ed-11d2-b252-00c04f681b71}。

Option Explicit
Sub Main()

   Dim oTestStream As New ADODB.Stream
   Dim oTestConnection As New ADODB.Connection
   Dim oTestCommand As New ADODB.Command

   oTestConnection.Open "provider=SQLXMLOLEDB.4.0;data provider=SQLNCLI10;data source=SqlServerName;initial catalog=AdventureWorks;Integrated Security=SSPI;"

   oTestCommand.ActiveConnection = oTestConnection
   oTestCommand.Properties("ClientSideXML") = "False"

   oTestCommand.CommandText = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'> " & _
        " <sql:xpath-query mapping-schema='mySchema.xml' > " & _
        "   root " & _
        "   </sql:xpath-query> " & _
        " </ROOT> "
   oTestStream.Open
   ' You need the dialect if you are executing a template.
   oTestCommand.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
   oTestCommand.Properties("Output Stream").Value = oTestStream
   oTestCommand.Properties("Base Path").Value = "c:\Schemas\SQLXML4\TemplateWithXPath\"
   oTestCommand.Properties("Mapping Schema").Value = "mySchema.xml"
   oTestCommand.Properties("Output Encoding") = "utf-8"
   oTestCommand.Execute , , adExecuteStream
   oTestStream.Position = 0
   oTestStream.Charset = "utf-8"
   Debug.Print oTestStream.ReadText(adReadAll)

End Sub

Sub Form_Load()
   Main
End Sub

這是結構描述：

<xsd:schema xmlns:xsd='http://www.w3.org/2001/XMLSchema'
   xmlns:sql='urn:schemas-microsoft-com:mapping-schema'>
 <xsd:element name= 'root' sql:is-constant='1'> 
    <xsd:complexType>
       <xsd:sequence>
         <xsd:element ref = 'Contact'/>
       </xsd:sequence>
    </xsd:complexType>
  </xsd:element>

  <xsd:element name='Contact' sql:relation='Person.Contact'>
     <xsd:complexType>
          <xsd:attribute name='ContactID' type='xsd:integer' />
          <xsd:attribute name='FirstName' type='xsd:string'/> 
          <xsd:attribute name='LastName' type='xsd:string' /> 
     </xsd:complexType>
   </xsd:element>
</xsd:schema>