sql:limit-field and sql:limit-value (SQLXML 4.0)
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
Save the schema that is provided in this example as SampleSchema.xml.
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
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)" />
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>