Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

Solved

Parsing the XML data to SQL Server

Posted on 2016-09-29

1 solution

Medium Priority

79 Views

Last Modified: 2016-09-30

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

Question by:asp123

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 32

Expert Comment

by:Pawan Kumar

ID: 418218882016-09-29

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

Author Comment

by:asp123

ID: 418219632016-09-29

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.

LVL 53

Expert Comment

by:Ryan Chong

ID: 418230242016-09-30

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

LVL 32

Accepted Solution

by:Pawan Kumar

Pawan Kumar earned 2000 total points

ID: 418230322016-09-30

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 !!

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

4 Certifications Experts Exchange Can Help You Earn

Article by: Gene

Ready to get certified? Check out some courses that help you prepare for third-party exams.

Q&A with SQL Expert Jim Horn

Article by: Experts Exchange

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.

Set Up Backup with Maintenance Plans (SQL Server)

Video by: Steve

Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Microsoft SQL Server

SQL - Updating and Deleting Data

Video by: Zia

Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…