Retrieving Unconsumed Data Using sql:overflow-field

XML and Internet Support

previous page next page
XML and Internet Support

Retrieving Unconsumed Data Using sql:overflow-field

When records are inserted in the database from an XML document using OPENXML, all the unconsumed data from the source XML document can be stored in a column. In retrieving data from the database using annotated schemas, the sql:overflow-field attribute can be specified to identify the column in the table in which the overflow data is stored.

This data is then retrieved in these ways:

  • Attributes stored in the overflow column are added to the element containing the sql:overflow-field annotation.

  • The subelements, and their descendents, stored in the overflow column in the database are added as subelements, following the content that is explicitly specified in the schema (no order is preserved).
Examples

To create working samples using these examples, you must create the nwind virtual directory (to access the Northwind database) and a virtual name of template type. For more information about creating the nwind virtual directory, see Creating the nwind Virtual Directory.

In creating working samples in each example, templates are used to specify XPath queries against the mapping XDR schema. There are different ways of using annotated XDR schemas in queries, for example, inline schemas and schemas in the URL. For more information, see Using Annotated XDR Schemas in Queries.

A. Specify sql:overflow-field for an <ElementType> in the XDR schema

The example assumes this table exists:

CREATE TABLE Customers2 (
   CustomerID       VARCHAR(10), 
   ContactName    VARCHAR(30), 
   OverflowData    NVARCHAR(200))
GO
INSERT INTO Customers2 VALUES (
      'ALFKI', 
      'Joe',
       N'<xyz><address>111 Maple, Seattle</address></xyz>')
GO

In this example, the mapping schema retrieves the unconsumed data stored in the OverflowData column of the Customers2 table. The sql:overflow-field attribute is specified on the <ElementType>.

<?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="Customers2" sql:overflow-field="OverflowData" >
    <AttributeType name="CustomerID" />
    <AttributeType name="ContactName" />

    <attribute type="CustomerID" />
    <attribute type="ContactName"/>
  </ElementType>
</Schema>

Testing a sample XPath query against the schema

  1. Save the schema (MySchema.xml) in the directory associated with the template virtual name that you have already created (or one of its subdirectories, in which case you must specify the relative path in the mapping-schema attribute).

  2. Create this template (MySchemaT.xml) and save it in the directory associated with the template virtual name. The query in the template selects a customer with the CustomerID of ALFKI. 
    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="MySchema.xml">
    Customers2
  </sql:xpath-query>
</ROOT>
  3. This URL executes the template: 
    http://IISServer/VirtualRoot/template/MySchemaT.xml

Here is the partial result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"> 
  <Customers2 CustomerID="ALFKI" ContactName="Joe"> 
    <address>111 Maple, Seattle</address> 
  </Customers2> 
</ROOT>

See Also

IIS Virtual Directory Management for SQL Server

Using XPath Queries

Accessing SQL Server Using HTTP

Executing Template Files Using HTTP

previous page start next page