Solved

Parse XML into tablle

Posted on 2011-03-18
7
302 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

730 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