Edit

Share via

Annotation Interpretation - sql:mapped

  • Article

Applies to: SQL Server Azure SQL Database

XML Bulk Load processes the sql:mapped annotation in the XSD schema as expected-that is, if the mapping schema specifies sql:mapped="false" for any element or attribute, XML Bulk Load does not attempt to store the associated data in the corresponding column.

XML Bulk Load ignores elements and attributes that are not mapped (either because they are not described in the schema, or because they are annotated in the XSD schema with sql:mapped="false"). All unmapped data goes into the overflow column, if such a column is specified by using sql:overflow-field.

For example, consider this XSD schema:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
<xsd:element name="ROOT" sql:is-constant="1">  
<xsd:complexType>  
<xsd:sequence>  
  <xsd:element name="Customers" sql:relation="Cust"  
                                sql:overflow-field="OverflowColumn" >  
   <xsd:complexType>  
       <xsd:attribute name="CustomerID"  type="xsd:integer" />  
       <xsd:attribute name="CompanyName" type="xsd:string" />  
       <xsd:attribute name="City"        type="xsd:string" />  
       <xsd:attribute name="HomePhone"   type="xsd:string"   
                                       sql:mapped="false" />  
    </xsd:complexType>  
  </xsd:element>  
</xsd:sequence>  
</xsd:complexType>  
</xsd:element>  
</xsd:schema>

Because the HomePhone attribute specifies sql:mapped="false", XML Bulk Load does not map this attribute to the corresponding column. The XSD schema identifies an overflow column (OverflowColumn) in which XML Bulk Load stores this unconsumed data.

To test a working sample

  1. Create the following table in the tempdb database:

    USE tempdb  
CREATE TABLE Cust  
          (CustomerID     int         PRIMARY KEY,  
           CompanyName    varchar(20) NOT NULL,  
           City           varchar(20) DEFAULT 'Seattle',  
           OverflowColumn nvarchar(200))  
GO

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

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

    <ROOT>  
  <Customers CustomerID="1111" CompanyName="Sean Chai"   
             City="NY" HomePhone="111-1111" />  
  <Customers CustomerID="1112" CompanyName="Dont Know"   
             City="LA" HomePhone="222-2222" />  
</ROOT>

  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.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:sql="urn:schemas-microsoft-com:xml-sql" >   
   <ElementType name="ROOT" sql:is-constant="1">  
      <element type="Customers" />  
   </ElementType>  
   <ElementType name="Customers" sql:relation="Cust"  
                             sql:overflow-field="OverflowColumn" >  
      <AttributeType name="CustomerID" />  
      <AttributeType name="CompanyName"  />  
      <AttributeType name="City"  />  
      <AttributeType name="HomePhone" />  
      <attribute type="CustomerID"  />  
      <attribute type="CompanyName"  />  
      <attribute type="City" />  
      <attribute type="HomePhone" sql:map-field="0" />  
   </ElementType>  
</Schema>