Skip to content

annotation-interpretation-sql-mapped.md

Latest commit

 

History

History
111 lines (95 loc) · 4.47 KB

annotation-interpretation-sql-mapped.md

File metadata and controls

111 lines (95 loc) · 4.47 KB
title sql:mapped (SQLXML)
description Learn how the SQLXML annotation sql:mapped is interpreted during the XML Bulk Load process.
author MikeRayMSFT
ms.author mikeray
ms.date 03/14/2017
ms.service sql
ms.subservice xml
ms.topic reference
helpviewer_keywords
mapped annotation
element mapping [SQLXML], XML Bulk Load
attribute mapping [SQLXML], XML Bulk Load
overflow data [SQLXML]
monikerRange =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current
ms.custom sfi-ropc-nochange

Annotation Interpretation - sql:mapped

[!INCLUDE 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 [!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.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>