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).