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:

Example

use MeasureMySkills;

go

create table MyXmlTable

(

       id int identity(1, 1) not null,

       MyXml xml not null

);

go

insert into MyXmlTable

values

(

       '<root1>

       <node1>

       some text

       </node1>

       </root1>'

);

select

       id,

       MyXml.query('/root1/node1') as data

from MyXmlTable;

select

       id,

       MyXml.exist('/root1/node1') as data

from MyXmlTable;

select

       id,

       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.

References

·         BOL reference on XML XQuery functions and XML DML

·         BOL reference on FOR XML

·         BOL reference on the xml data type

·         BOL reference on CREATE XML SCHEMA COLLECTION

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