Monday, August 9, 2010

Basic questions on using XML in SQL Server 2005

The XML Basics:
  • XML stands for EXtensible Markup Language.
  • XML is a markup language similar to HTML.
  • XML was designed to store data and not for display the data.
  • An XML document contains XML Elements.
  • Anyone can define their own tags.
  • XML elements can have attributes, just like HTML.
  • XPath uses path expressions to navigate in XML documents.
  • XPath contains a library of standard functions.
  • XQuery was designed to query XML data.

The XML data type:
  • XML Data is one of the new data types introduced in SQL Server 2005.
  • You can create columns, variables with data type XML.
  • XML Data type allows you to store XML documents and fragments in SQL Server.
  • You can index the XML column.

XML schema collection:
  • Optionally XML schema collection can be associated with a column, or a variable which is of the xml data type.
  • The XML schema collections are used to validate XML and the XML is said to be typed.
  • If the XML is not associated with schema but well-formed then the XML is called untyped.
  • SELECT * FROM sys.XML_Schema_collections returns the information about the registered schemas.

Retrieve XML data:

  • In SQL Server 2005, you can store XML data in rowset or XML column.
  • You can execute SQL queries to return results as XML instead of standard rowsets.
  • The FOR XML clause converts the result sets from a query into an XML structure, and it provides different modes of formatting:
           *  FOR XML RAW
               Returns XML with each row representing an XML element.
               Each column value is mapped to an attribute with the same name as column name.

           *  FOR XML AUTO
               Returns Query results as nested XML elements.

           *  FOR XML PATH
               Returns specific values by indicating the column names for which you need to retrive the data.

           *  FOR XML EXPLICIT
               Returns XML that have format as given in the SELECT statement.


The following are the available methods to query and manipulate XML data.

Method- Description
Value- Retrives a single value.
Nodes- Shreds an XMl column into relational data.
Query- Query an XML column.
Modify- Specifies XML data manipulation statements.
Exist- Checks if XML query returns data.


1. Which of the following are well formed XML? Choose all that apply.
(a) <Question/>
(b) <Question>
(c) <Question>NULL</question>
(d) 'Question'

2. If an XML data is asociated with Schema collections then it is said to be typed.
(a) True
(b) False

3. If you execute the following query what will be your output. Choose any one answer.

USE AdventureWorks;

SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119

<row ProductModelID="122" Name="All-Purpose Bike Stand" />
<row ProductModelID="119" Name="Bike Wash" />

<Name>All-Purpose Bike Stand</Name>
<Name>Bike Wash</Name>

<Name>All-Purpose Bike Stand</Name>
<Name>Bike Wash</Name>

<ProductModel ProductModelID="122" Name="All-Purpose Bike Stand"/>
<ProductModel ProductModelID="119" Name="Bike Wash"/>

Show Answers: