Menu Request Demo

XML Data in the data warehouse

Date:
22 November 2007
Author:
Douglas Barrett

Frequently we have to import data that is given to us in the form of XML documents.  WhereScape RED supports importing XML data directly into a load table provided that the XML file is well structured (it can be opened in IE) and is accompanied by an XML schema (XSD) file that describes the structure of the data - this schema file allows WhereScape RED to automatically create the load table structure. 

If the XML does not have a corresponding XSD schema file, or it is of another standard, or a file contains XML fragments then WhereScape RED can leverage SQL Server's and Oracle's XML data type support.  Once XML data is loaded into an XML data type then the built-in extensions to SQL can be used to query the XML data. 

Although XML data can be kept in an XML data type, queried, and  even indexed it is usually much easier to shred the XML into relational tables as in most people's opinion XML is hard to read,verbose and requires XML functions to query.  XML documents are built up of elements and attributes.  Elements can be nested (like multi-value fields).  In SQL  Server you can use column transforms using the value function to access element attributes eg

 XMLData.value('(row/c2)[1]','VARCHAR(255)')

This example accesses the attribute called c2 of the row element and converts it to a varchar datatype.

 To access nested elements the nodes function can be used in the FROM clause, for example:

FROM load_t24
CROSS APPLY load_t24.XMLData.nodes('row/c90') as T(C)

This example splits the nested elements of the c90 element into a collection of rows.

Combinations of the value, query, and nodes functions can be used in column transformations and FROM clauses in WhereScape RED to shred any XML structures into database tables without resorting to custom code.

CATEGORIES:

Comments 0

Leave a Comment

No comments.