Edit

Share via

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

  • Article

Applies to: 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 Microsoft 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>