MCTS 70 – 433 working with XML data

Working with XML data

Retrieve relational data as XML

To retrieve a result set as XML rather than as a tabular data set use the FOR XML clause.

The FOR XML clause has four options (RAW, AUTO, PATH and EXPLICIT)

RAW – returns each row as an XML element; you can specify a root directive by appending (‘element’), ROOT(”element). XML handles NULLs by simply removing the attribute element. You can alternatively use XSINIL to handle NULLs within XML. The RAW option has an elements directive which will return each attribute as an element. It is worth noting that the elements directive and XSINIL are also available in the AUTO option too. 

AUTO – has support for hierarchies; but only one hierarchical path. The ORDER BY clause is very important because the XML document is built as the rows are returned.

EXPLICIT – allows you to create virtually any XML structure but it is very clumbersome. The Explicit option requires that columns are defined in a particular way and a tag and parent columns be defined; the parent column references the tag column which is a integer.

PATH – more powerful than RAW and AUTO and less clumbersome than EXPLICIT.

Nesting FOR XML queries

The subqueries should include the TYPE option to ensure that the XML is returned as XML data type rather than in textual form e.g.

SELECT
columns
(
SELECT
columns
FROM
table
FOR XML PATH('element name'), TYPE
)
FROM
Table
FOR XML PATH('element name');

Transform XML data into relational data

The XPath value methods nodes and value can be used to transform XML data into a tabular result set.

Another method is OpenXML. This method uses the sp: sp_xml_preparedocument which takes an integer and an XML document e.g.

SET @xmlDocument = '
<Record>
  <Employee>
    <BusinessEntityID>1</BusinessEntityID>
    <LoginID>adventure-works\ken0</LoginID>
    <SalariedFlag>1</SalariedFlag>
    <Name>Executive</Name>
  </Employee>
  <Employee>
    <BusinessEntityID>2</BusinessEntityID>
    <LoginID>adventure-works\terri0</LoginID>
    <SalariedFlag>1</SalariedFlag>
    <Name>Engineering</Name>
  </Employee>
</Record>'
'
EXEC sp_xml_preparedocument @iPointer OUTPUT, @xmlDocument

Next you can use the OpenXML method to return values from within the XML as tabular results.

SELECT * FROM OPENXML(@iPointer,'/Record/Employee',2) 
WITH (LoginID VARCHAR(50), Name VARCHAR(50), SalariedFlag BIT) XmlTable

OpenXML takes the following arguments:

document handle – which is an integer i.e. iPointer in the example above.

Row Pattern – which is the XML nodes to be processed as rows.

Flags – this determines whether to use element or attribute centric mappings; attribute is default.

The sp: sp_xml_removedocument @iPointer removes the document handle from memory.

EXEC sp_xml_removedocument @XMLDocPointer

Query XML data

XPath and XQuery

element node: <node></node>

attribute node: <node attrib=”value”></node>

example XML document

<node>
<node1 attribute1="value" attribute2="value" />
</node>

XPath expressions:

selects an attribute:

SELECT xmlColumn.value('(/node/node1/@attribute1)[1]','data type') 
as [Column Title] FROM xmlTable

selects an attribute which equals a variable:

DECLARE @sqlvariable DATATYPE = 'value'

SELECT xmlColumn.value('(/node/node1/@attribute1)[1]','data type') 
as [Column Title] FROM xmlTable 
WHERE xmlColumn.value('(/node/node1/@attribute1)[1]','data type') 
= @sqlvariable

selects an element:

SELECT xmlColumn.value('(/node/node1/node2)[1]','data type') 
as [Column Title] FROM xmlTable WHERE xmlTableId = value

inserts an attribute:

DECLARE @sqlvariable DATATYPE = 'value'

DECLARE @xml XML = '

<node>
<node1>
</node1>
</node>'

SET @xml.modify('insert attribute attibute1 
{sql:variable("@sqlvariable")} into (/node/node1)[1]')

INSERT INTO xmlTable
VALUES
(
@xml
)

updates an attribute:

UPDATE xmlTable
SET xmlColumn.modify('replace value of (/node/node1/@attribute1)[1] 
with sql:variable("@sqlvariable")')
WHERE xmlTableId = value

XQuery expressions: – returns an XML fragment rather than a single value or tabular result set.

selects an attribute:

SELECT xmlColumn.query('/node/node1[@attribute1]') as [Column Title] 
FROM xmlTable

selects an element:

SELECT xmlColumn.query('/node/node1/node2') as [Column Title] 
FROM xmlTable 
WHERE xmlColumn.exist('/node/node1/node2') = (1 = true, 0 = false)

Manage XML data

XML data types are stored in a binary representation rather than in a string format. The data type can be typed or untyped; typed XML data type must adhere to an associated XML SCHEMA COLLECTION, whereas untyped allows anything.

An XML SCHEMA COLLECTION defines the elements and attributes of an XML column; any data being inserted or updated must adhere to the elements and attribute type e.g. if the attribute is a integer then 0, 1, 2,3… are acceptable values whereas one, two, three… aren’t. If the XML SCHEMA COLLECTION requires more attributes then the XML SCHEMA COLLECTION must be dropped from the column, modified and re-appiled to the column.

XML indexes are created on XML columns using the CREATE PRIMARY XML INDEX pxml_Table_xmlColumn ON schema.Table (xmlColumn); The PRIMARY argument defines a clustered index. The clustered index is built using the clustered index key and an XML node identifier.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s