Solved

Parse XML into tablle

Posted on 2011-03-18
7
303 Views
Last Modified: 2012-06-21
I need to read the XML below with a select statement so I can manipulate and read into a SQL Table.
In the image below the selct statement would repeat the packtNum and RunDate every line
The items in the repeating <Record> tags would each have its own record line and a column for each tag..example

packtNum   RunDate   SeqNumber    LienRefNum, blah...blah...blah...FirstName   MiddleInit   LastName  RejectReason

next record

 Screen Print
0
Comment
Question by:lrbrister
[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
  • 4
  • 2
7 Comments
 
LVL 3

Expert Comment

by:kumarnimavat
ID: 35166998
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 35169254
Hi Irbrister,
I created a sample XML file based on the print screen which you supplied.

I then created a SQL Server table to store the XML from this file and bulk loaded it in using the embedded T-SQL code.
 
CREATE TABLE XmlImportBuffer
(
	xmlFileName VARCHAR(300) NOT NULL,
	xml_data XML NOT NULL
)
GO

DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'c:\data.xml'

-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET

EXEC('INSERT INTO XmlImportBuffer(xmlFileName, xml_data)

SELECT ''' + @xmlFileName + ''', xmlData
FROM(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO

Open in new window


I then used the following T-SQL(with explanatory comments) to query the XML content in the way you asked above - This is not  a complete example, but it should give you the main idea
 
declare @xml_text varchar(max)		-- variable to store the XML content
declare @i int						-- xml document handle

-- get XML content from XML data type field in table into a variable
select @xml_text = cast(xml_data as varchar(max)) from xmlImportBuffer

-- prepare the XML document
exec sp_xml_preparedocument @i OUTPUT, @xml_text

-- Get the PacketNum and RunDate at the <Document> node level
-- and rpeat these fields(CROSS APPLY) for every record at the <Record> node level
select * from
	openxml(@i,'/Document',2)
	with
	(
		PacketNum nvarchar(100),
		RunDate bigint
	)
CROSS APPLY
(
	select * from
	openxml(@i,'/Document/Record',2)
	with
	(
		SeqNumber int,
		LineRefNumber bigint,
		Ref2 nvarchar(50),
		FilingOffice nvarchar(50)
	)
) s

-- remove the XML dcoument
exec sp_xml_removedocument @i

Open in new window


Come back to me with specific questions
Data.xml
0
 

Author Comment

by:lrbrister
ID: 35189001
BCUNNEY:
This is wonderful and almost exactly what I need.  One question...how would I do a cross apply to get the FirstName, LastName etc nodes?
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 500 total points
ID: 35189079
Hi Ibrister,
Off the top of my head, this may work
 
select * from
	openxml(@i,'/Document',2)
	with
	(
		PacketNum nvarchar(100),
		RunDate bigint
	)
CROSS APPLY
(
	select * from
	openxml(@i,'/Document/Record',2)
	with
	(
		SeqNumber int,
		LineRefNumber bigint,
		Ref2 nvarchar(50),
		FilingOffice nvarchar(50)
	)
) s
CROSS APPLY
(
	select * from
	openxml(@i,'/Document/Record/FirstDebtorName/IndividualName/',2)
	with
	(
		FirstName nvarchar(50),
		LastName nvarchar(50)
	)
) d

Open in new window


I have this set up in my home environment, so I can fully test later today
0
 

Author Comment

by:lrbrister
ID: 35189143
BCUNNEY:
That did it...minor change on the final one and removed last / from right side

Thanks a ton!
CREATE TABLE XmlImportBuffer  
(  
        xmlFileInfo VARCHAR(300) NOT NULL,  
        xml_data XML NOT NULL  
)  
GO 

DECLARE @xmlFileName VARCHAR(300)  
SELECT @xmlFileName = 'c:\testXML.xml'  
  
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET  
  
EXEC('INSERT INTO XmlImportBuffer(xmlFileInfo, xml_data)  
  
SELECT ''' + @xmlFileName + ''', xmlData  
FROM(  
SELECT *  
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA  
) AS FileImport (XMLDATA)  
')  
GO 


declare @xml_text varchar(max)          -- variable to store the XML content  
declare @i int                                          -- xml document handle  
  
-- get XML content from XML data type field in table into a variable  
select @xml_text = cast(xml_data as varchar(max)) from xmlImportBuffer  
  
-- prepare the XML document  
exec sp_xml_preparedocument @i OUTPUT, @xml_text  
  
-- Get the PacketNum and RunDate at the <Document> node level  
-- and rpeat these fields(CROSS APPLY) for every record at the <Record> node level  
select * from  
        openxml(@i,'/Document',2)  
        with  
        (  
                PacketNum nvarchar(100),  
                RunDate bigint  
        )  
CROSS APPLY  
(  
        select * from  
        openxml(@i,'/Document/Record',2)  
        with  
        (  
                SeqNumber int,  
                LineRefNumber bigint,  
                Ref2 nvarchar(50),  
                FilingOffice nvarchar(50)  
        )  
) s  
CROSS APPLY  
(  
        select * from  
        openxml(@i,'/Document/Record/FirstDebtorName/IndividualName',2)  
        with  
        (  
                FirstName nvarchar(50),  
                LastName nvarchar(50)  
        )  
) d 

 
  
-- remove the XML dcoument  
exec sp_xml_removedocument @i

Open in new window

0
 

Author Closing Comment

by:lrbrister
ID: 35189151
Wonderful job and great examples!  Thanks a ton.
0
 

Author Comment

by:lrbrister
ID: 35189203
BCUNNEY:
Please watch for follow up question.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question