Simple questions on XML

XQuery Examples:

If the following XML fragment is stored in a column with XML datatype,

<EMPLOYEE EMP_ID="AAA013" ADDRESS1="83 Railway Colony" ADDRESS2="Perambur" CITY="Chennai" STATE="TN" />

this select statement uses the value() method to retrive the attribute values.

SELECT 
txtxml.value('(//@EMP_ID)[1]','varchar(500)') EMP_ID,
txtxml.value('(//@ADDRESS1)[1]','varchar(500)') ADDRESS1,
txtxml.value('(//@ADDRESS2)[1]','varchar(500)') ADDRESS2,
txtxml.value('(//@CITY)[1]','varchar(500)') CITY,
txtxml.value('(//@STATE)[1]','varchar(500)') STATE
FROM Employee WHERE dept_code='AAA013'

The output of the above query will be as follows:

EMP_ID	ADDRESS1	ADDRESS2	CITY	STATE
AAA013	83 Railway Colony	Perambur	Chennai	TN

---

1. If you need to store XML document and manipulations are done only at client side, which one of the following datatype would you recommend?

(a) varbinary(max)
(b) xml
(c) Text
(d) char

2. The following query

SELECT EmpID, Emp_XML_Data.query('/Employee/Name') AS Name

will return the following output:

EmpID	Name
1	<Name> Sudhakar</Name>
2	<Name> Vignesh</Name>
3	<Name> Arivu</Name>

What modification to the SELECT will give the output without XML tags as follows?

EmpID	Name
1	Sudhakar
2	Vignesh
3	Arivu

(a) SELECT EmpID, Emp_XML_Data.Value('/Employee/Name') AS Name
(b) SELECT EmpID, Emp_XML_Data.Nodes('/Employee/Name') AS Name
(c) By using REPLACE function to remove the XML tags.
(d) We cannot remove the XML tags from the output.


Show Answers:

1. (a) [ - Because no XML manipulation is done at server side, Varbinary(max) datatype is sufficient. ]
2. (a) [ - Value() method returns the text node or value between the elements.

Hide Answers (X).

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.

Methods:

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.

---

Questions:

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;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID=122 or ProductModelID=119
FOR XML AUTO, ELEMENTS;
GO

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

(b)
<row>
<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
</row>
<row>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>
</row>

(c)
<ProductModel>
<ProductModelID>122</ProductModelID>
<Name>All-Purpose Bike Stand</Name>
</ProductModel>
<ProductModel>
<ProductModelID>119</ProductModelID>
<Name>Bike Wash</Name>
</ProductModel>

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


Show Answers:

1. (a), (d)
2. (a)
3. (c)

Hide Answers (X).