Tuesday, August 10, 2010

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.

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:

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

(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 comment: