annotation-interpretation-sql-limit-field-and-sql-limit-value.md

title	sql:limit-field and sql:limit-value (SQLXML)
description	Learn how to use the SQLXML annotations sql:limit-field and sql:limit-value to filter data when using XML Bulk Load.
author	MikeRayMSFT
ms.author	mikeray
ms.date	03/14/2017
ms.service	sql
ms.subservice	xml
ms.topic	reference
helpviewer_keywords	limiting values [SQLXML] limit-value annotation limit-field annotation
monikerRange	=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current
ms.custom	sfi-ropc-nochange

Annotation Interpretation - sql:limit-field and sql:limit-value

[!INCLUDE SQL Server Azure SQL Database] XML Bulk Load processes the sql:limit-field and sql:limit-value annotations per their definition. For more information, see Filtering Values Using sql:limit-field and sql:limit-value (SQLXML 4.0).

For example, suppose a database contains the following tables:

• Customer (CustomerID, CompanyName)

• Addresses (CustomerID, StreetAddress, AddressType)

A customer can have many addresses, and each address has an address type associated with it (for example, a shipping address or a billing address).

Now consider this XML view of these tables as specified in the following annotated XSD 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="Address"  
        child-key="CustomerID" />  
  </xsd:appinfo>  
</xsd:annotation>  
  
  <xsd:element name="Customer" sql:relation="Customer" >  
   <xsd:complexType>  
        <xsd:attribute name="CustomerID"   type="xsd:int" />   
        <xsd:attribute name="CompanyName"  type="xsd:string" />  
        <xsd:attribute name="BillTo"   
                       type="xsd:string"   
                       sql:relation="Address"   
                       sql:field="StreetAddress"  
                       sql:limit-field="AddressType"  
                       sql:limit-value="billing"  
                       sql:relationship="CustAddr" >  
        </xsd:attribute>  
        <xsd:attribute name="ShipTo"   
                       type="xsd:string"   
                       sql:relation="Address"   
                       sql:field="StreetAddress"  
                       sql:limit-field="AddressType"  
                       sql:limit-value="shipping"  
                       sql:relationship="CustAddr" >  
        </xsd:attribute>  
    </xsd:complexType>  
  </xsd:element>  
</xsd:schema>  

Upon receiving this schema and XML data, XML Bulk Load inserts the value that is specified for the BillTo attribute into the StreetAddress column of the CustAddress table along with the "billing" value for the AddressType column.

Similarly, XML Bulk Load inserts the value that is specified for the ShipTo attribute into the StreetAddress column along with the "shipping" value in the AddressType column.

To test a working sample

1. Save the schema that is provided in this example as SampleSchema.xml.

2. Create these tables:

   CREATE TABLE Customer(  
                    CustomerID     int         PRIMARY KEY,  
                    CompanyName    varchar(20) NOT NULL)  
   GO  
   CREATE TABLE Address(  
                     CustomerID     int        FOREIGN KEY REFERENCES   
                                                Customer(CustomerID),   
                     StreetAddress  varchar(50),  
                     AddressType    varchar(10))  
   GO  
   

3. Save the following sample data as SampleXMLData.xml:

   <Customer CustomerID="1111" CompanyName="Sean Chai" City="NY"   
                BillTo="111 Maple (Billing) "   
                ShipTo="111 Maple (Shipping)" />  
   <Customer CustomerID="1112" CompanyName="Dont Know" City="LA"   
                BillTo="222 Spruce (Billing)"   
                ShipTo="222 Spruce (Shipping)" />  
   

4. To execute XML Bulk Load, save and execute this [!INCLUDEmsCoName] Visual Basic Scripting Edition (VBScript) example as Sample.vbs:

   set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")  
   objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;database=tempdb;integrated security=SSPI"  
   objBL.ErrorLogFile = "c:\error.log"  
   objBL.XMLFragment = True  
   objBL.CheckConstraints=True  
   objBL.Execute "c:\SampleSchema.xml", "c:\SampleXMLData.xml"  
   set objBL=Nothing  
   

This is the equivalent XDR 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="Address" >  
                <sql:relationship   
                        key="CustomerID"  
                        key-relation="Customer"  
                        foreign-relation="Address"  
                        foreign-key="CustomerID" />  
    </attribute>  
    <attribute type="ShipTo"   
                sql:limit-field="AddressType"  
                sql:limit-value="shipping"  
                sql:field="StreetAddress"  
                sql:relation="Address" >  
                <sql:relationship   
                     key="CustomerID"  
                     key-relation="Customer"  
                     foreign-relation="Address"  
                     foreign-key="CustomerID" />  
    </attribute>  
</ElementType>  
</Schema>