Filtering Values by Using sql:limit-field and sql:limit-value (XDR Schema)

1. Create the following two tables in the tempdb database:

   USE tempdb
   CREATE TABLE Customer (CustomerID int primary key, 
                          CompanyName varchar(30))
   CREATE TABLE Addresses(CustomerID int, 
                          StreetAddress varchar(50),
                          AddressType varchar(10))
   

2. Add the sample data:

   INSERT INTO Customer values (1, 'Company A')
   INSERT INTO Customer values (2, 'Company B')
   
   INSERT INTO Addresses values
               (1, 'Obere Str. 57 Berlin', 'billing')
   INSERT INTO Addresses values
               (1, 'Avda. de la Constitución 2222México D.F.', 'shipping')
   INSERT INTO Addresses values
               (2, '120 Hanover Sq., London', 'billing')
   INSERT INTO Addresses values
               (2, 'Forsterstr. 57, Mannheim', 'shipping')
   

3. Copy the schema code above and paste it into a text file. Save the file as LimitFieldValueXdr.xml.

4. Copy the following template and paste it into a text file. Save the file as LimitFieldValueXdrT.xml in the same directory where you saved LimitFieldValueXdr.xml. The query in the template selects all records in the Customer table.

   <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
     <sql:xpath-query mapping-schema="LimitFieldValueXdr.xml">
       /Customer
     </sql:xpath-query>
   </ROOT>
   

   The directory path specified for the mapping schema (LimitFieldValueXdr.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:

   mapping-schema="C:\MyDir\LimitFieldValueXdr.xml"
   

5. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

   For more information, see Using ADO to Execute SQLXML 4.0 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customer CustomerID="1" CompanyName="Company A" 
          BillTo="Obere Str. 57 Berlin" 
          ShipTo="Avda. de la Constitución 2222México D.F." /> 
  <Customer CustomerID="2" CompanyName="Company B" 
          BillTo="120 Hanover Sq., London" 
          ShipTo="Forsterstr. 57, Mannheim" /> 
</ROOT>

1. Create two tables in the tempdb database:

   USE tempdb
   CREATE TABLE Orders ([OrderID] int NOT NULL ) ON [PRIMARY]
   ALTER TABLE Orders WITH NOCHECK ADD 
   CONSTRAINT [PK_Orders] PRIMARY KEY  CLUSTERED (
      [OrderID]
    )  ON [PRIMARY] 
   CREATE TABLE [OrderDetails] (
      [OrderID] int NOT NULL ,
      [ProductID] int NOT NULL ,
      [UnitPrice] money NULL ,
      [Quantity] smallint NOT NULL ,
      [Discount] real NOT NULL 
   ) ON [PRIMARY]
   

2. Add the sample data:

   INSERT INTO Orders ([OrderID]) values (10248)
   INSERT INTO Orders ([OrderID]) values (10250)
   INSERT INTO Orders ([OrderID]) values (10251)
   INSERT INTO Orders ([OrderID]) values (10257)
   INSERT INTO Orders ([OrderID]) values (10258)
   INSERT INTO [OrderDetails] ([OrderID],[ProductID],[UnitPrice],[Quantity],[Discount]) values (10248,11,14,12,0)
   INSERT INTO [OrderDetails] ([OrderID],[ProductID],[UnitPrice],[Quantity],[Discount]) values (10250,51,42.4,35,0.15)
   INSERT INTO [OrderDetails] ([OrderID],[ProductID],[UnitPrice],[Quantity],[Discount]) values (10251,22,16.8,6,0.05)
   INSERT INTO [OrderDetails] ([OrderID],[ProductID],[UnitPrice],[Quantity],[Discount]) values (10257,77,10.4,15,0)
   INSERT INTO [OrderDetails] ([OrderID],[ProductID],[UnitPrice],[Quantity],[Discount]) values (10258,2,15.2,50,0.2)
   

3. Save the schema (LimitFieldValue.xml) in a directory.

4. Create the following test script (TestQuery.vbs), modify MyServer to the name of your SQL Server computer and save it in the same directory as you used in the previous step to save the schema:

   Set conn = CreateObject("ADODB.Connection")
   conn.Open "Provider=SQLOLEDB;Data Source=MyServer;Database=tempdb;Integrated Security=SSPI"
   conn.Properties("SQLXML Version") = "sqlxml.4.0" 
   Set cmd = CreateObject("ADODB.Command")
   Set stm = CreateObject("ADODB.Stream")
   Set cmd.ActiveConnection = conn
   stm.open
   result ="none"
   strXPathQuery="/root"
   DBGUID_XPATH = "{EC2A4293-E898-11D2-B1B7-00C04F680C56}"
   'DBGUID_MSSQLXML = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
   cmd.Dialect = DBGUID_XPATH
   cmd.CommandText = strXPathQuery
   cmd.Properties("Mapping schema") = "LimitFieldReal.xml"
   cmd.Properties("Output Stream").Value = stm
   cmd.Properties("Output Encoding") = "utf-8"
   WScript.Echo "executing for xml query"
   On Error Resume Next
   cmd.Execute , ,1024
   if err then
   Wscript.Echo err.description
   Wscript.Echo err.Number
   Wscript.Echo err.source
   On Error GoTo 0
   else
   stm.Position = 0
   result  = stm.ReadText
   end if
   WScript.Echo result
   Wscript.Echo "done"
   

5. Execute the TestQuery.vbs file by clicking on it in Windows Explorer.

   This is the result:

   <root>
     <Order OrderID="10248"/>
     <Order OrderID="10250"/>
     <Order OrderID="10251"/>
     <Order OrderID="10257"/>
     <Order OrderID="10258">
       <orderDetail OrderID="10258" 
                    ProductID="2" 
                    Discount="0.2" 
                    Quantity="50"/>
     </Order>
   </root>