Assuming you have an XML file structured as follows
<questionnaire>
<question>
<id>1</id>
<text>Which of the following is TRUE?</text>
<option1>6 is a perfect number.</option1>
<option2>All prime numbers are odd.</option2>
<option3>None of the Above</option3>
<answer>B</answer>
<explanation>B is FALSE because 2 is prime and even.
Perfect Numbers are such that the sum of their prime factors and 1 equals the number. Since the prime factors of 6 are 2 and 3 and 2+3+1 = 6, then 6 must be perfect.</explanation>
</question>
…
</questionnaire>
and there are a thousand more <question> … </question> set in it but you are interested only in retrieving the id and answer. Using SQL Server 2008, what SQL statements should you use?
Here it is:
-------------------------------------------------
DECLARE @FilePath AS VARCHAR(50)
DECLARE @Query AS NVARCHAR(MAX)
DECLARE @QuestionData XML
-- set the path to xml file
SET @FilePath = 'C:\Users\test\questions.xml'
SET @Query = N'SELECT @data = CAST(BulkColumn AS XML) FROM OPENROWSET(BULK N''' + @FilePath + ''', SINGLE_BLOB) AS XmlData'
EXEC sp_executesql @Query, N'@data AS XML OUTPUT', @QuestionData OUTPUT
DECLARE @hdoc int
EXEC sp_xml_preparedocument @hdoc OUTPUT, @QuestionData
SELECT
*
FROM
OPENXML (@hdoc, '/questionnaire/question', 10)
WITH (
id INT,
answer CHAR(1)
)
EXEC sp_xml_removedocument @hdoc
-------------------------------------------------
Simple eh?
