Skip to content

annotation-interpretation-sql-mapped.md

Latest commit

 

History

History
116 lines (100 loc) · 4.73 KB

annotation-interpretation-sql-mapped.md

File metadata and controls

116 lines (100 loc) · 4.73 KB
title sql:mapped (SQLXML)
description Learn how the SQLXML annotation sql:mapped is interpreted during the XML Bulk Load process.
ms.date 03/14/2017
ms.prod sql
ms.prod_service database-engine, sql-database
ms.reviewer
ms.technology xml
ms.topic reference
helpviewer_keywords
mapped annotation
element mapping [SQLXML], XML Bulk Load
attribute mapping [SQLXML], XML Bulk Load
overflow data [SQLXML]
sql:mapped
column mapping [SQLXML]
ms.assetid 7042741e-ce4d-4912-9c4a-d77194a028fc
author MightyPen
ms.author genemi
ms.custom seo-lt-2019
monikerRange =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

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>