Export (0) Print
Expand All

Executing XPath Queries with Namespaces (SQLXMLOLEDB Provider)

SQL Server 2000

Executing XPath Queries with Namespaces (SQLXMLOLEDB Provider)

XPath queries can include namespaces. If the schema elements are namespace qualified (use a target namespace), the XPath queries against the schema must specify the namespace.

Because using the wildcard character (*) is not supported in Microsoft® SQLXML 3.0, you must specify the XPath query by using a namespace prefix. To resolve this prefix, use the namespaces property to specify the namespace binding.

In the following example, the XPath query specifies namespaces by using the wildcard character (*) and the local-name() and namespace-uri() XPath functions. This XPath query returns all the elements where the local name is Employee and the namespace URI is urn:myschema:Employees.

/*[local-name() = 'Employee' and namespace-uri() = 'urn:myschema:Employees']

In SQLXML 3.0, this XPath query must be specified with a namespace prefix. An example is x:Employee, where x is the namespace prefix. Consider the following XSD schema:

<schema xmlns="http://www.w3.org/2001/XMLSchema"
<complexType name="EmployeeType">
  <attribute name="EID" sql:field="EmployeeID" type="ID"/>
  <attribute name="FName" sql:field="FirstName" type="string"/>
  <attribute name="LName" sql:field="LastName"/> 
<element name="Employee" type="emp:EmployeeType" sql:relation="Employees"/>

Because this schema defines the target namespace, an XPath query (such as "Employee") against the schema must include the namespace.

This is a sample Microsoft Visual Basic® application that executes an XPath query (x:Employee) against the preceding XSD schema. To resolve the prefix, the namespace binding is specified by using the namespaces property.

In the code, you must provide the name of the instance of Microsoft SQL Server™ in the connection string.

Option Explicit
Private Sub Form_Load()
    Dim con As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim stm As New ADODB.Stream
    con.Open "provider=sqlxmloledb.3.0;data provider=sqloledb;data source=SqlServerName;initial catalog=Northwind;Integrated Security=SSPI;"
    Set cmd.ActiveConnection = con
    cmd.Properties("Output Stream").Value = stm
    cmd.Properties("Output Encoding") = "utf-8"
    cmd.Properties("Mapping schema") = "C:\DirectoryPath\emp-ex.xml"
    cmd.Properties("namespaces") = "xmlns:x='urn:myschema:Employees'"
    '  Debug.Print "Set Command Dialect to DBGUID_XPATH"
    cmd.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"
    cmd.CommandText = "x:Employee"
    cmd.Execute , , adExecuteStream 
    stm.Position = 0
    Debug.Print stm.ReadText(adReadAll)
End Sub

To test this application

  1. Save the sample XSD schema in a folder.

  2. Create a Visual Basic executable project, and copy the code in it. Change the specified directory path as appropriate.

  3. Add the following project reference:
    "Microsoft ActiveX Data Objects 2.6 Library"
  4. Execute the application.

This is the partial result:

<y0:Employee xmlns:y0="urn:myschema:Employees" 
             LName="Davolio" EID="1" FName="Nancy"/>
<y0:Employee xmlns:y0="urn:myschema:Employees" 
             LName="Fuller" EID="2" FName="Andrew"/>


The prefixes that are generated in the XML document are arbitrary, but they map to the same namespace.

For information about specifying a target namespace in XDR schemas, see "Specifying a Target namespace Using sql:target-namespace" in SQL Server Books Online.

© 2014 Microsoft