Filtering Values by Using sql:limit-field and sql:limit-value (XDR Schema)
Important
This topic is included as a reference for legacy applications. No future development work will be done on this feature. Avoid using this feature in new development work. Instead, use annotated XSD schemas to create XML views. For more information, see Introduction to Annotated XSD Schemas (SQLXML 4.0). You can convert existing annotated XDR schemas to XSD schemas. For more information, see Converting Annotated XDR Schemas to Equivalent XSD Schemas (SQLXML 4.0).
You can limit rows returned from a database query based on some limiting value. These annotations are used to identify the database column that contains the 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. sql:limit-field is used to qualify the join relationship specified using <sql:relationship>. sql:limit-field must be used on an element or attribute that has <sql:relationship> specified.
The sql:limit-value annotation is used to specify the limited value in the column specified in a sql:limit-field annotation. This 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 float and real (Transact-SQL), SQLXML 4.0 performs conversion on the sql:limit-value as specified in XML schemas as an nchar and nvarchar (Transact-SQL) 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 the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.
A. Limit 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 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 StreetAddress column in the Addresses relation. The values 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:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="Customer" sql:relation="Customer" >
<AttributeType name="CustomerID" />
<AttributeType name="CompanyName" />
<AttributeType name="BillTo" />
<AttributeType name="ShipTo" />
<attribute type="CustomerID" />
<attribute type="CompanyName" />
<attribute type="BillTo"
sql:limit-field="AddressType"
sql:limit-value="billing"
sql:field="StreetAddress"
sql:relation="Addresses" >
<sql:relationship
key="CustomerID"
key-relation="Customer"
foreign-relation="Addresses"
foreign-key="CustomerID" />
</attribute>
<attribute type="ShipTo"
sql:limit-field="AddressType"
sql:limit-value="shipping"
sql:field="StreetAddress"
sql:relation="Addresses" >
<sql:relationship
key="CustomerID"
key-relation="Customer"
foreign-relation="Addresses"
foreign-key="CustomerID" />
</attribute>
</ElementType>
</Schema>
To test a sample XPath query against the schema
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))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')Copy the schema code above and paste it into a text file. Save the file as LimitFieldValueXdr.xml.
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"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>
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:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="root" sql:is-constant="1">
<element type="Order"/>
</ElementType>
<ElementType name="orderDetail" sql:relation="OrderDetails">
<AttributeType name="OrderID" />
<AttributeType name="ProductID" />
<AttributeType name="Discount" />
<AttributeType name="Quantity" />
<attribute type="OrderID" />
<attribute type="ProductID" />
<attribute type="Discount" />
<attribute type="Quantity" />
</ElementType>
<ElementType name="Order" sql:relation="Orders">
<AttributeType name="OrderID"/>
<attribute type="OrderID"/>
<element type="orderDetail"
sql:limit-field="Discount"
sql:limit-value="2.0000000e-001">
<sql:relationship key="OrderID"
key-relation="Orders"
foreign-relation="OrderDetails"
foreign-key="OrderID"/>
</element>
</ElementType>
</Schema>
To test a sample XPath query against the schema
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]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)Save the schema (LimitFieldValue.xml) in a directory.
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"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
Reference
Using XPath Queries in SQLXML 4.0