70-433 SQL Server - Query() method on XML elements

Setup example XML element:

declare @myXML xml

SET myXML = '

   

    eve

    matthey

 

 

    stef

    amar

 

 

    miriam

    tricky

 

' 

Query example XML element:

Select @myXML.query('{/Root/employee[@employee=2]/surname}};

Expected results

amar

Reusing myXML, we could use sp_xml_preparedocument and OPENXML

DECLARE @docHandle int

EXEC sp_xml_preparedocument @docHandle, @myXML

70-433 SQL Server XML Indexes - basic usage examples

The emp table has two columns "xml data type" columns: emp_xml & dept_xml

each column have it's own "PRIMARY XML INDEX" and then also multiple (secondary) "XML INDEX" (FOR PATH, FOR VALUE or FOR PROPERTY)

CREATE PRIMARY XML INDEX emp_xml_pri ON emp(emp_xml);

CREATE XML INDEX emp_xml_value ON emp(emp_xml) USING XML INDEX emp_xml_pri FOR VALUE;

CREATE XML INDEX emp_xml_path ON emp(emp_xml)  USING XML INDEX emp_xml_pri FOR PATH;

CREATE XML INDEX emp_xml_prop ON emp(emp_xml)  USING XML INDEX emp_xml_pri FOR PROPERTY;

70-433 SQL Server XML Indexes

XML Indexes are a new concept for me, this Microsoft support page gives some good background.

Let start with the following slightly controversial (over-simplification):

Although not everyone would agree, one of the main reasons for the success of the relational database has been the inclusion of the SQL language. SQL is a set-based declarative language. As opposed to COBOL (or most .NET-based languages for that matter), when you use SQL, you tell the database what data you are looking for, rather than how to obtain that data. The SQL query processor determines the best plan to get the data you want and then retrieves the data for you. As query-processing engines mature, your SQL code will run faster and better (less I/O and CPU) without the developer making changes to the code. What development manager wouldn't be pleased to hear that programs will run faster and more efficiently with no changes to source code because the query engine gets better over time?

So how can we access data in XML format efficiently:

In the early days of XML, imperative programming (navigation through the XML DOM) was all the rage. The XQuery language in general and XQuery inside the database in particular make it possible for the query engine writers to approach the task of optimizing queries against XML. The chances of success are good because these folks have 20 years or so of practical experience optimizing SQL queries against the relational data model. The SQL Server 2005 implementation of XQuery over the built-in XML data type holds the same promise of a declarative language, with optimization through a query engine

The 70-433 exam appears to require basic familarity with the following XML functions:

Table 1. XML Data Type Functions

Name	Signature	Usage
exist	bit = X.exist(string xquery)	Checks for existence of nodes, returns 1 if any output returned from query, otherwise 0
value	scalar = X.value( string xquery, string SQL type)	Returns a SQL scalar value from a query cast to specified SQL data type
query	XML = X.query(string xquery)	Returns an XML data type instance from query
nodes	X.nodes(string xquery)	Table-value function used for XML to relational decomposition. Returns one row for each node that matches the query.
modify	X.modify(string xml-dml)	A mutator method that changes the XML value in place

The article goes on to detail how this mapped and represented in internal SQL Server database objects.. interesting stuff.