We help IT Professionals succeed at work.

Parsing the XML data to SQL Server

asp123
asp123 asked
on
I have dozens of these kind of file from a client that I need to store in the database as separate columns and other important identifier information.

How can I extract the Question: What is the color of Orange and 4 response options for it? I also want to extract all information from the "key" node with values in the ID, EVAL attribute as well.

<TestItem  CLASS="MOAT" METHOD="Multiple">
  <div id="stim" style="width:455px;text-align:center;margin:10px 10px 10px 0px;height:580px;background-color:#ffffff;padding:10px;">
    <div style=" margin:10px; text-align:center;background-color:#ffffff;">
      <table>
        <tr>
          <td valign="middle" align="right">
            <img src="../images/test1/test1.jpg" />
          </td>
          <td valign="middle" align="left">
            <div class="inItemEnclosedbubble">
              What is the color of Orange?
            </div>
          </td>
        </tr>
      </table>
      <br/>
      <br/>
      <div style="text-align:left;margin:auto;">
       
<div style="height:30px"></div>
      <ssmcblock ID="TEST_81330">
        <distractor ID="A">
          <div class="innerDistractor">
            Orange.
          </div>
        </distractor>
        <distractor ID="B">
          <div class="innerDistractor">
            Blue.

          </div>
        </distractor>
        <distractor ID="C">
          <div class="innerDistractor">
            Red.

          </div>
        </distractor>

        <distractor ID="D">
          <div class="innerDistractor">
            Green.
          </div>
        </distractor>



      </ssmcblock>
      </div>
    </div>

    </div>

 
  <Key>
    <part ID="TEST_81330" EVAL="TESTMC">4</part>
  </Key>  
</TestItem>
Test.xml
Comment
Watch Question

View Solution Only

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Here it is...

CREATE TABLE WorkingTable(Data XML)
Go

INSERT INTO WorkingTable
SELECT * FROM OPENROWSET (BULK 'E:\Pawan.xml', SINGLE_BLOB) AS data
GO

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM WorkingTable

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT Id,text
FROM OPENXML(@hDoc, '/TestItem/div/div')
WHERE LocalName = '#text' AND text LIKE '%Orange%' OR  text LIKE '%Blue%' OR  text LIKE '%Red%' OR  text LIKE '%Green%'

EXEC sp_xml_removedocument @hDoc

---

Open in new window



Enjoy !! Pawan
asp123

Author

Commented:
Is there a way for me to get the text value along with class value?

            <div class="inItemEnclosedbubble">
              What is the color of Orange?
            </div>

          <div class="innerDistractor">
            Blue.
          </div>

I want the data as

inItemEnclosedbubble, What is the color of Orange?
innerDistractor,  Blue.
Ryan ChongSoftware Team Lead

Commented:
try something like this:
IF OBJECT_ID('tempdb..#WorkingTable') IS NOT NULL DROP TABLE #WorkingTable
Go

CREATE TABLE #WorkingTable(Data XML)
Go

INSERT INTO #WorkingTable
SELECT * FROM OPENROWSET (BULK 'D:\yourFolder\Test.xml', SINGLE_BLOB) AS data
GO

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM #WorkingTable

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT *
FROM OPENXML(@hDoc, '//distractor/div')
WITH (
	Question		varchar(100) '/TestItem/div/div/table/tr/td[2]/div',
	QuestionClass	varchar(100) '/TestItem/div/div/table/tr/td[2]/div/@class',
	AnswerNo		varchar(10) '../@ID',
    Answer			varchar(100) '..' ,
    AnswerClass		varchar(50) '@class'   
) 

EXEC sp_xml_removedocument @hDoc

Open in new window

Database Expert
Awarded 2016
Top Expert 2016
Commented:
Here it is with output...

IF OBJECT_ID('tempdb..#WorkingTable') IS NOT NULL DROP TABLE #WorkingTable
Go

CREATE TABLE #WorkingTable(Data XML)
Go

INSERT INTO #WorkingTable
SELECT * FROM OPENROWSET (BULK 'E:\Pawan.xml', SINGLE_BLOB) AS data
GO

DECLARE @XML AS XML, @hDoc AS INT
SELECT @XML = Data FROM #WorkingTable

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT DISTINCT *
FROM OPENXML(@hDoc, '//distractor/div')
WITH (
	Question		varchar(100) '/TestItem/div/div/table/tr/td[2]/div',
	QuestionClass	varchar(100) '/TestItem/div/div/table/tr/td[2]/div/@class'	
) 
UNION ALL
SELECT *
FROM OPENXML(@hDoc, '//distractor/div')
WITH 
(
	AnswerNo		varchar(10) '../@ID',    
    AnswerClass		varchar(50) '@class'  
) 

EXEC sp_xml_removedocument @hDoc

--

Open in new window

--


Output

Question                                        QuestionClass
What is the color of Orange?      inItemEnclosedbubble
A                                                      innerDistractor
B                                                      innerDistractor
C                                                      innerDistractor
D                                                      innerDistractor

Enjoy !!