Storing XML in Relational Databases
by Igor Dayen | Pages: 1, 2, 3, 4

Microsoft SQL Server 2000

SQL to XML Mapping

SQL Server's SQL-to-XML and XML-to-SQL mapping rules employ different syntaxes. Mapping details are discussed in the sections on extraction and storing.

Extracting XML from the database

Mapping between database columns and XML elements or attributes is defined by means of AS aliases in a SELECT:

<database column> AS [Element Name! Nesting Level! Attribute Name! Directive]

The document top level is assigned level 1, as below. By default, column data is mapped onto attribute values. The directive "element" may be used to change this default setting.

The process of generating an XML document from the database has two steps.

Step 1. Create As-aliases to atomic elements in your desired output XML; the alias defines the parent/child relationships between elements. The table below shows the aliases for our example document.

FXTRADE       /* LEVEL=1 */    
    CURRENCY1   [FXTRADE!1!CURRENCY1]    
    CURRENCY2   [FXTRADE!1!CURRENCY2]    
    AMOUNT      [FXTRADE!1!AMOUNT]    
    SETTLEMENT  [FXTRADE!1!SETTLEMENT]    
    ACCOUNT   /* LEVEL=2  */    
        BANKCODE     [ACCOUNT!2!BANKCODE]    
        BANKACCT     [ACCOUNT!2!BANKACCT]    

Step 2. Define the output tree structure in SQL. Each level of the tree is defined through a SELECT statement, thereafter the levels are combined together into the tree by means of a UNION ALL statement. The level-1 SELECT statement introduces the names of atomic elements on all levels. Each SELECT statement introduces a tree level tag and its parent tag. There is a single record in the result set corresponding to the tree root, as defined in the first SELECT statement below.

SELECT    
    1          AS    Tag,    
    NULL       AS    Parent,    
    NULL       AS    [FXTRADE!1!CURRENCY1],    
    NULL       AS    [FXTRADE!1!CURRENCY2],    
    NULL       AS    [FXTRADE!1!AMOUNT],    
    NULL       AS    [FXTRADE!1!SETTLEMENT],    
    NULL       AS    [ACCOUNT!2!BANKCODE],    
    NULL       AS    [ACCOUNT!2!BANKACCT]    
FROM            
    FXTRADE    
UNION ALL    
SELECT    
    2,    
    1,    
    FXTRADE.CURRENCY1,    
    FXTRADE.CURRENCY2,    
    FXTRADE.AMOUNT,    
    FXTRADE.SETTLEMENT,    
    ACCOUNT.BANKCODE,    
    ACCOUNT.BANKACCT    
FROM    
    FXTRADE,    ACCOUNT    
WHERE    
    FXTRADE.ACCOUNT = ACCOUNT.ID    
ORDER    BY    [ACCOUNT!2!BANKCODE],    
        [ACCOUNT!2!BANKACCT]    
FOR    XML    EXPLICIT, ELEMENTS    

The FOR XML constructs an XML document by analyzing tags and AS-aliases in the combined row set. The keyword EXPLICIT selects the most flexible, user-defined mode of constructing XML document. Another mode, AUTO, constructs XML document by applying default rules. The keyword ELEMENTS models SQL columns at element level; otherwise, the default is that columns are modeled on attribute level.

Storing XML in the database

Storing XML documents employs OPENXML, a new row set function, similar to table or view. OPENXML can be used for inserts or updates, or for SELECT INTO target tables. OPENXML simplified syntax is shown below:

OPENXML    (<XML document handler>, <path pattern>, <flags>)    
WITH     (Schema | Table)

The process of storing an XML document has three steps.

1. Obtain an XML document handler by compiling XML document into internal DOM representation, using the stored procedure sp_xml_preparedocument.

2. Construct a schema by associating schema fields with atomic XML elements.

   XML elements are defined by a path pattern (absolute base path) plus a relative element path. Element-centric mapping is indicated by a flags value of 2. Existing tables can be used instead of a schema, with field names equal to XML names.

3. Remove the compiled XML document from memory by using the stored procedure sp_xml_removedocument.

These steps are illustrated by the following example.

DECLARE @idoc int    
DECLARE @doc varchar(1000)    
SET @doc ='    
<FXTRADE>    
    <CURRENCY1>GBP</CURRENCY1>    
    <CURRENCY2>JPY</CURRENCY2>    
    <AMOUNT>10000</AMOUNT>    
    <SETTLEMENT>20010325</SETTLEMENT>    
    <ACCOUNT>    
        <BANKCODE>812</BANKCODE>    
        <BANKACCT>00365888</BANKACCT>    
    </ACCOUNT>    
</FXTRADE>'    
-- Create internal DOM representation of the XML document.    
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc    
-- Execute a SELECT statement using OPENXML row set provider.    
SELECT *    
FROM OPENXML (@idoc, '/FXTRADE/ACCOUNT', 2)    
WITH (    
  CURRENCY1     CHAR (3),       '../@CURRENCY1',    
  CURRENCY2     CHAR (3),       '../@CURRENCY2',    
  AMOUNT        NUMERIC (18,2), '../@AMOUNT',    
  SETTLEMENT    DATETIME,       '../@SETTLEMENT',    
  BANKCODE      VARCHAR (100),  '@BANKCODE',    
  BANKACCT      VARCHAR (100),  '@BANKACCT' )
EXEC sp_xml_removedocument @idoc    

Summary

With Microsoft SQL Server 2000, the extraction and storage of XML documents does not use symmetrical syntax. Extraction extends a SELECT-clause by use of the FOR XML construct. Storing introduces a row set function OPENXML, analogous to a table or view. Extraction mapping rules are based on (a) introducing tags for specifying tree level and (b) associating table fields with parent/child relationships between XML document elements. Storing restructures an XML document into a flat schema or table; "field-element" association is defined using XPath notation.

Pages: 1, 2, 3, 4

Next Page