Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Handling Simple XML Using T-SQL

By Eli Leiba,

This article describes how to traverse a simple XML file that has only 2 attributes (name and value, like a dictionary). And get the results in a single column in SQL SERVER result set

When dealing with interfaces to other applications that are not SQL SERVER based or even not even RDBMS based, the best (and simplest) media for communicating is the XML file.

When the data required can be arranged in a dictionary manner (name and value), the remote application can make an XML file in the following form at :

<ROOT>
<LINE>
        <NAME>NAME1</NAME>
        <VALUE>VALUE1</VALUE>
</LINE>
<LINE>
        <NAME>NAME2</NAME>
        <VALUE>VALUE2</VALUE>
</LINE>
.
.
.
.
.
</ROOT>

I wrote a procedure that gets the XML file full path and name and traverses the XML file and the output is a single column result set where the odd rows are the "name" tags value and the even rows are the "value" tags value.

I used the BULK INSERT t-sql Statement combined with dynamic sql execution in order to "bulk copy" the XML file Inside SQL Server. This is the code implementation of the XML traversing  process :

create  proc sp_translate_simple_xml
 (@XMLfile varchar(100))

as

begin

declare @idoc int
declare @doc varchar(8000)
declare @xml_line varchar(200)
declare @bulkinscmd varchar(500)

set @doc =''

-- insert XML file into temporary table

Create table #tempXML
  (line varchar(8000))

set @bulkinscmd = 'BULK INSERT #tempXML FROM ' +
        '''' + @XMLfile + ''''

exec (@bulkinscmd) 

DECLARE xml_cursor CURSOR
   FOR SELECT * FROM #tempXML

-- create XML string in SQL SERVER memory
OPEN xml_cursor 
FETCH NEXT FROM xml_cursor INTO @xml_line
WHILE @@FETCH_STATUS = 0
 BEGIN
   SET @doc = @doc + rtrim(ltrim(@xml_line))
   FETCH NEXT FROM xml_cursor INTO @xml_line
 END 

close xml_cursor

deallocate xml_cursor

drop table #tempXML

--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT statement using OPENXML rowset provider
SELECT  text 
 FROM  OPENXML (@idoc, '/ROOT/LINE')
 WHERE text is not null

EXEC sp_xml_removedocument @idoc
END 
Go

Example of a call to the procedure. Suppose an XML called dict.xml is in C:\sql. That looks like this:

<ROOT>
<LINE>
       <NAME>SUBJECT</NAME>
       <VALUE>ELI SECTION</VALUE>
</LINE>
<LINE>
       <NAME>THE PLANT</NAME>
       <VALUE>IEC factory</VALUE>
</LINE>
<LINE>
       <NAME>CREATE DATE</NAME>
       <VALUE>01/07/2004</VALUE>
</LINE>
</ROOT>
after running the procedure :
use master 
go

exec sp_translate_simple_xml 'c:\sql\dict.xml'

this will traverse the dict.xml file and produce a single column result set with name in odd row numbers and values in even row numbers. We will get the following results (using this file - dict.xml):

text
--------------------------
SUBJECT
ELI SECTION
THE PLANT
IEC factory
CREATE DATE
01/07/2004

(6 row(s) affected)

Conclusion

The procedure can be used as an ideal tool for communicating with applications that cannot link directly to SQL server , must use an intermediate media and a little amount of data is required for communication.

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail: iecdba@hotmail.com)

Total article views: 12863 | Views in the last 30 days: 9
 
Related Articles
FORUM

Creating CURSOR

Creating Cursor with conditional query

FORUM

Cursor

cursor

FORUM

Could not complete cursor operation because the table schema changed after the cursor was declared => Dynamics AX

Could not complete cursor operation because the table schema changed after the cursor was declared =...

FORUM

Cursors

How to Update using Cursors?

FORUM

How to Group and batch select/cursor results in stored procedure

How to Groupand batch select/cursor results for e-mail

Tags
basics    
xml    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones