通过

sql:limit-field 和 sql:limit-value (SQLXML 4.0)

XML 大容量加载按照定义处理 sql:limit-field 和 sql:limit-value 批注。有关详细信息，请参阅使用 sql:limit-field 和 sql:limit-value 筛选值 (SQLXML 4.0)。

例如，假定一个包含以下各表的数据库：

  • Customer（CustomerID、CompanyName）

  • Addresses（CustomerID、StreetAddress、AddressType）

客户可以具有多个地址，并且每个地址都具有一个关联的地址类型（如发货地址或帐单地址）。

现在，请考虑在以下带批注的 XSD 架构中指定的这些表的此 XML 视图：

<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>

收到此架构和 XML 数据时，XML 大容量加载将为 BillTo 属性指定的值插入到 CustAddress 表的 StreetAddress 列，并将“billing”值插入到 AddressType 列。

类似地，XML 大容量加载将为 ShipTo 属性指定的值插入到 StreetAddress 列，并将“shipping”值插入到 AddressType 列。

测试工作示例

  1. 将本示例提供的架构另存为 SampleSchema.xml。

  2. 创建以下各表：

    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. 将下面的示例数据另存为 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. 若要执行 XML 大容量加载，请将此 Microsoft Visual Basic Scripting Edition (VBScript) 示例另存为 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

以下是等效的 XDR 架构：

<?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>