Retrieving Unconsumed Data Using sql:overflow-field (XDR Schema)

  • Article

Important

This topic is included as a reference for legacy applications. No future development work will be done on this feature. Avoid using this feature in new development work. Instead, use annotated XSD schemas to create XML views. For more information, see Introduction to Annotated XSD Schemas (SQLXML 4.0). You can convert existing annotated XDR schemas to XSD schemas. For more information, see Converting Annotated XDR Schemas to Equivalent XSD Schemas (SQLXML 4.0).

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 the following examples, you must meet certain requirements. For more information, see Requirements for Running SQLXML Examples.

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

The example assumes this table exists in the tempdb database:

USE tempdb
CREATE TABLE Customers2 (
CustomerID       VARCHAR(10), 
ContactName    VARCHAR(30), 
AddressOverflow    NVARCHAR(500))

GO
INSERT INTO Customers2 VALUES (
'ALFKI', 
'Joe',
'<Address>
  <Address1>Maple St.</Address1>
  <Address2>Apt. E105</Address2>
  <City>Seattle</City>
  <State>WA</State>
  <Zip>98147</Zip>
 </Address>')
GO

In this example, the mapping schema retrieves the unconsumed data stored in the AddressOverflow 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="AddressOverflow" >
    <AttributeType name="CustomerID" />
    <AttributeType name="ContactName" />

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

To testing a sample XPath query against the schema

  1. Copy the schema code above and paste it into a text file. Save the file as OverflowXdr.xml.

  2. Copy the following template and paste it into a text file. Save the file as OverflowXdrT.xml in the same directory where you saved OverflowXdr.xml.

    <ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="OverflowXdr.xml">
    Customers2
  </sql:xpath-query>
</ROOT>

    The directory path specified for the mapping schema (OverflowXdr.xml) is relative to the directory where the template is saved. An absolute path also can be specified, for example:

    mapping-schema="C:\MyDir\OverflowXdr.xml"

  3. Create and use the SQLXML 4.0 Test Script (Sqlxml4test.vbs) to execute the template.

    For more information, see Using ADO to Execute SQLXML 4.0 Queries.

Here is the result set:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <Customers2 CustomerID="ALFKI" ContactName="Joe">
    <Address1>Maple St.</Address1> 
    <Address2>Apt. E105</Address2> 
    <City>Seattle</City> 
    <State>WA</State> 
    <Zip>98147</Zip> 
  </Customers2>
</ROOT>

See Also

Reference

Using XPath Queries in SQLXML 4.0

Help and Information

Getting SQL Server 2005 Assistance