Skip to content

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

Latest commit

 

History

History
164 lines (141 loc) · 6.24 KB

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

File metadata and controls

164 lines (141 loc) · 6.24 KB
title sql:limit-field and sql:limit-value (SQLXML 4.0) | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-server-2016
ms.reviewer
ms.suite
ms.technology
dbe-xml
ms.tgt_pltfrm
ms.topic reference
helpviewer_keywords
limiting values [SQLXML]
limit-value annotation
limit-field annotation
sql:limit-field
sql:limit-value
filtering [SQLXML]
ms.assetid 402c21cf-9566-463f-a928-f94270c11db3
caps.latest.revision 21
author douglaslMS
ms.author douglasl
manager jhubbard

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

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>