Monday, May 21, 2012

Implement Database Objects: Work with XML data

(This is part of the Study Guide series, 70-464)

Microsoft’s Measured Skill description: This objective may include but is not limited to: implement XML; use XML (Query, Input, Output); retrieve relational data as XML; FOR XML; design a strategy to transform XML into relational data; design a strategy to query and modify XML data; understand XML data types and their schemas and interoperability, limitations, and restrictions; implement XML schemas and handling of XML data; when and when not to use XML, including XML namespaces; import and export XML

What I see:
·         query XML
·         Retrieve relational data as XML
·         XML data types
·         XML schemas

Query XML
                Querying XML can be achieved through the use of XQuery (introduced in SQL Server 2005).  There are a number of XML functions that are used.  Below are examples of a few of them:

use MeasureMySkills;

create table MyXmlTable
       id int identity(1, 1) not null,
       MyXml xml not null

insert into MyXmlTable
       some text

       MyXml.query('/root1/node1') as data
from MyXmlTable;

       MyXml.exist('/root1/node1') as data
from MyXmlTable;

       MyXml.value('(/root1/node1)[1]', 'varchar(40)') as data
from MyXmlTable;

                The modify() XQuery function uses the XML DML language specification.  These functions and this XML DML can be referenced here.

Retrieve relational data as XML
                The reverse of shredding XML into relational data (above) is retrieving relational data in the form of XML.  This is done utilizing FOR XML in your queries.  The four FOR XML options are RAW, AUTO, PATH, and EXPLICIT.  Detailed descriptions and examples can be found here on BOL.

XML data types
                There is a data specifically made for XML:  It’s appropriated called xml.  Upon declaration of an xml variable, you have the option to specify the XML schema the data will conform to.  For further reading on the xml data type, reference BOL.

XML schemas
                XML schemas and namespaces are used to created “typed” XML, meaning they are defined and checked against a set of structure rules they must follow.  For more information on how to create and alter, please see BOL.

·         BOL reference on FOR XML

If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at

No comments:

Post a Comment