Parse XML into tablle

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
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Barry CunneyConnect With a Mentor Commented:
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
 
kumarnimavatCommented:
0
 
Larry Bristersr. DeveloperAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Barry CunneyConnect With a Mentor Commented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
Wonderful job and great examples!  Thanks a ton.
0
 
Larry Bristersr. DeveloperAuthor Commented:
BCUNNEY:
Please watch for follow up question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.