[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Parse XML into tablle

Posted on 2011-03-18
7
Medium Priority
?
308 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 2000 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 17

Assisted Solution

by:Barry Cunney
Barry Cunney earned 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

649 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