Extracting Data from an XML file in MS SQL Server

 

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?