Skip to content

Latest commit

 

History

History
303 lines (253 loc) · 12.6 KB

filtering-values-using-sql-limit-field-and-sql-limit-value-sqlxml-4-0.md

File metadata and controls

303 lines (253 loc) · 12.6 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords monikerRange
Filter with sql:limit-field and sql:limit-value (SQLXML)
Learn how to use the sql:limit-field and sql:limit-value annotations in SQLXML 4.0 to filter the data returned by a query on the basis of a limiting value.
MikeRayMSFT
mikeray
03/16/2017
sql
xml
reference
annotated XSD schemas, filtering values
limiting values [SQLXML]
limit-value annotation
limit-field annotation
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Filtering Values Using sql:limit-field and sql:limit-value (SQLXML 4.0)

[!INCLUDE SQL Server Azure SQL Database] You can limit rows that are returned from a database query on the basis of some limiting value. The sql:limit-field and sql:limit-value annotations are used to identify the database column that contains limiting values and to specify a specific limiting value to be used to filter the data returned.

The sql:limit-field annotation is used to identify a column that contains a limiting value; it is allowed on each mapped element or attribute.

The sql:limit-value annotation is used to specify the limited value in the column that is specified in the sql:limit-field annotation. The sql:limit-value annotation is optional. If sql:limit-value is not specified, a NULL value is assumed.

Note

When working with a sql:limit-field where the mapped SQL column is of type real, SQLXML 4.0 performs conversion on the sql:limit-value as specified in XML schemas as an nvarchar specified value. This requires that decimal limit values be specified using full scientific notation. For more information, see Example B below.

Examples

To create working samples using these examples, you need to have the following installed:

  • Microsoft [!INCLUDEssNoVersion] Native Client

  • MDAC 2.6 or later

In these examples, templates are used to specify XPath queries against the mapping XSD schema.

A. Limiting the customer addresses returned to a specific address type

In this example, a database contains two tables:

  • Customer (CustomerID, CompanyName)

  • Addresses (CustomerID, AddressType, StreetAddress)

A customer can have a shipping address and/or a billing address. The AddressType column values are Shipping and Billing.

This is the mapping schema in which the ShipTo schema attribute maps to the StreetAddress column in the Addresses relation. The values that are returned for this attribute are limited to only shipping addresses by specifying the sql:limit-field and sql:limit-value annotations. Similarly, the BillTo schema attribute returns only the billing address of a customer.

This is the schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
<xsd:annotation>  
  <xsd:appinfo>  
    <sql:relationship name="CustAddr"  
        parent="Customer"  
        parent-key="CustomerID"  
        child="Addresses"  
        child-key="CustomerID" />  
  </xsd:appinfo>  
</xsd:annotation>  
  
  <xsd:element name="Customer" sql:relation="Customer" >  
   <xsd:complexType>  
        <xsd:sequence>  
        <xsd:element name="BillTo"   
                       type="xsd:string"   
                       sql:relation="Addresses"   
                       sql:field="StreetAddress"  
                       sql:limit-field="AddressType"  
                       sql:limit-value="billing"  
                       sql:relationship="CustAddr" >  
        </xsd:element>  
        <xsd:element name="ShipTo"   
                       type="xsd:string"   
                       sql:relation="Addresses"   
                       sql:field="StreetAddress"  
                       sql:limit-field="AddressType"  
                       sql:limit-value="shipping"  
                       sql:relationship="CustAddr" >  
        </xsd:element>  
        </xsd:sequence>  
        <xsd:attribute name="CustomerID"   type="xsd:int" />   
        <xsd:attribute name="CompanyName"  type="xsd:string" />  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>
To test a sample XPath query against the schema

  1. Create 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 2222 M?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 LimitFieldValue.xml.

  4. Create the following template (LimitFieldValueT.xml), and save it in the same where you saved the schema (LimitFieldValue.xml) in the previous step:

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

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

    mapping-schema="C:\MyDir\LimitFieldValue.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 Queries.

This is the result:

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

B. Limiting results based on a discount value of type real data

In this example, a database contains two tables:

  • Orders (OrderID)

  • OrderDetails (OrderID, ProductID, UnitPrice, Quantity, Price, Discount)

This is the mapping schema in which the OrderID attribute on the order details maps to the OrderID column in the orders relation. The values that are returned for this attribute are limited to only those that have a value of 2.0000000e-001 (0.2) as specified for the Discount attribute using the sql:limit-field and sql:limit-value annotations.

This is the schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
  <xsd:annotation>  
   <xsd:appinfo>  
    <sql:relationship name="OrderOrderDetails"  
        parent="Orders"  
        parent-key="OrderID"  
        child="OrderDetails"  
        child-key="OrderID" />  
   </xsd:appinfo>  
  </xsd:annotation>  
  <xsd:element name="root" sql:is-constant="1">  
   <xsd:complexType>  
     <xsd:sequence>  
       <xsd:element name="Order" sql:relation="Orders" >  
          <xsd:complexType>  
             <xsd:sequence>  
                <xsd:element name="orderDetail"   
                       sql:relation="OrderDetails"   
                       sql:limit-field="Discount"                       sql:limit-value="2.0000000e-001"  
                       sql:relationship="OrderOrderDetails">  
                   <xsd:complexType>  
                     <xsd:attribute name="OrderID"   />   
                     <xsd:attribute name="ProductID" />   
                     <xsd:attribute name="Discount"  />   
                     <xsd:attribute name="Quantity"  />   
                     <xsd:attribute name="UnitPrice" />   
                   </xsd:complexType>  
                </xsd:element>  
            </xsd:sequence>  
           <xsd:attribute name="OrderID"/>   
          </xsd:complexType>  
       </xsd:element>  
     </xsd:sequence>  
   </xsd:complexType>  
  </xsd:element>  
</xsd:schema>
To test a sample XPath query against the schema

  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}"  
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>

See Also

float and real (Transact-SQL)
nchar and nvarchar (Transact-SQL)
Installing SQL Server Native Client
Using Annotated XSD Schemas in Queries (SQLXML 4.0)