• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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
0
lrbrister
Asked:
lrbrister
  • 4
  • 2
2 Solutions
 
kumarnimavatCommented:
0
 
Barry CunneyCommented:
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
 
lrbristerAuthor 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Barry CunneyCommented:
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
 
lrbristerAuthor 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
 
lrbristerAuthor Commented:
Wonderful job and great examples!  Thanks a ton.
0
 
lrbristerAuthor Commented:
BCUNNEY:
Please watch for follow up question.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now