You have a table that contains an XML column named XMLData1. The column contains the following nodes:
• Title
• Description
• Author
• Weight
• NumPages
You need to design a stored procedure that will return only the title and description in a tabular result set. The document must be developed by using minimum amount of effort.
How should you extract the information?
A. Execute sp_preparedocument.
B. Use XMLData1.Exists() in a select statement.
C. Use XMLData1.Query() in a select statement.
D. Use FOR XML PATH in a select statement.
Correct Answer: A
Explanation/Reference:
None of the available choices is correct. There are 2 ways to produce tabular result set from XML. The first one is to use nodes() method. For example:
DECLARE @T1 TABLE (ID int, XMLData1 xml);
INSERT INTO @T1 VALUES
(1, ‘<Root><Title>Title1</Title><Description>Description1</Description><Autor>Autor1</Autor></Root>’)
,(2, ‘<Root><Title>Title2</Title><Description>Description2</Description><Autor>Autor2</Autor></Root>’)
,(3, ‘<Root><Title>Title3</Title><Description>Description3</Description><Autor>Autor3</Autor></Root>’)
SELECT C.value(‘Title[1]’, ‘VARCHAR(20)’) AS Title
,C.value(‘Description[1]’, ‘VARCHAR(20)’) AS Description
FROM @T1
CROSS APPLY XMLData1.nodes(‘/Root’) as T2(C)
WHERE ID = 1;
The query returns the following:
Title Description
-------------------- --------------------
Title1 Description1
The second way is to use OPENXML together with sp_xml_preparedocument. For example:
DECLARE @T1 TABLE (ID int, XMLData1 xml);
INSERT INTO @T1 VALUES
(1, ‘<Root><Title>Title1</Title><Description>Description1</Description><Autor>Autor1</Autor></Root>’)
,(2, ‘<Root><Title>Title2</Title><Description>Description2</Description><Autor>Autor2</Autor></Root>’)
,(3, ‘<Root><Title>Title3</Title><Description>Description3</Description><Autor>Autor3</Autor></Root>’)
DECLARE @docHandle int;
DECLARE @xml xml
SELECT @xml = XMLData1 FROM @T1 WHERE ID = 1;
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xml
SELECT *
FROM OPENXML (@docHandle, ‘/Root’,2)
WITH (Title varchar(20), Description varchar(20))
EXEC sp_xml_removedocument @docHandle
GO
The query returns the following:
Title Description
-------------------- --------------------
Title1 Description1
You should not use XMLData1.Exists() method because it has nothing to do with converting XML to tabular representation.
You should not use .query() method and FOR XML clause because they are doing the opposite of what is required in the matter.
• The .query (‘XQuery’) method specifies an XQuery against an instance of the xml data type and returns an instance of untyped XML.
• FOR XML clause retrieves formal results of a SQL query as XML
And finally, you should not use sp_preparedocument stored procedure because it does not exist. When you try to run it the error will be returned:
Could not find stored procedure ‘sp_preparedocument’.
Obviously there is a typo in the name. It should be written as sp_xml_preparedocument. Even though we don’t pay attention on this, the answer is still not complete.
The correct answer should be to use sp_xml_preparedocument, OPENXML and sp_xml_removedocument.
