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 FOR XML
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at sqlsalt@gmail.com.
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s