(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
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:
create table MyXmlTable
id int identity(1, 1) not null,
MyXml xml not null
insert into MyXmlTable
MyXml.query(‘/root1/node1’) as data
MyXml.exist(‘/root1/node1’) as data
MyXml.value(‘(/root1/node1)’, ‘varchar(40)’) as data
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 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.
If there are any comments, questions, issues, or suggestions please feel free to leave a comment below or email me at firstname.lastname@example.org.