Solved

Accessing XML document FROM T-SQL

Posted on 2004-08-08
15
547 Views
Last Modified: 2008-02-07
How can I access an .xml FILE from t-sql (query analyzer) ?

(I need an example)

0
Comment
Question by:MargusLehiste
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 2

Assisted Solution

by:DaveHavard
DaveHavard earned 50 total points
ID: 11751252
declare @idoc int
declare @doc varchar(250)
select @doc='
<root>
      <object name="test1">
            <child value="1"/>
      </object>
      <object name="test2">
            <child value="2"/>
      </object>
      <object name="test3">
            <child value="3"/>
      </object>
</root>'
exec sp_xml_preparedocument @idoc output, @doc

select      *
from      openxml(@idoc, '//root/object/child')
with      (
      ObjectName varchar(25) '../@name',
      ChildValue int '@value'
)

exec sp_xml_removedocument @idoc

Here is how to read xml. To read from a file rather than a variable change the @doc to be the file path. Beware the xml file must be well formed. sp_xml_preparedocument is far stricter with files than variables.

Dave
0
 
LVL 1

Author Comment

by:MargusLehiste
ID: 11757355
Getting ERROR:

Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 5
XML parsing error: Invalid at the top level of the document.

Here is what i executed:

declare @doc varchar(250)
select @doc='G:\emp.XML'

declare @testdoc int
exec sp_xml_preparedocument @testdoc OUTPUT, @doc

select * from
openxml(@testdoc,N'/emp/employee')
WITH(
name varchar(20) './name',
phone varchar(20) './phone',
email varchar(20) './email'
)

exec sp_xml_removedocument @testdoc


emp.XML :

<emp>
      <employee>
            <name> Margus Lehiste </name>
            <phone> 718-333-3333 </phone>
            <email>margusL@mpow.com</email>
      </employee>
      <employee>
            <name>Nelson</name>
            <phone>409-3777</phone>
            <email/>
      </employee>
      <employee>
            <name>Judy</name>
            <phone></phone>
            <email>JudyP@mpow.com</email>
      </employee>
</emp>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11757389
Unfortunately you cannot use a file path in the xmltext paramenter, it must be the Xml document itself. From BOL

<quote>
Is the original XML document. The MSXML parser parses this XML document.  xmltext is a text (char, nchar, varchar, nvarchar, text, or ntext) parameter. The default value is NULL, in which case an internal representation of an empty XML document is created.
</quote>
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 1

Author Comment

by:MargusLehiste
ID: 11757417
so how would i be able to accomplish what i want - have an XML document and retrieve a SQL result ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11757472
Reading the Xml document and passing it into SQL Server is best accomplished using a front end language like VB or C#.  That is not to say it cannot be done, it is just not a good idea.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11758548
So do you want to make this a T-SQL only solution, even though it is not recommended?
0
 
LVL 1

Author Comment

by:MargusLehiste
ID: 11759250
Yes - I would like to make it T-SQL only solution
(it's for theoretical reasons only - i do not use XML @ development - but I teach SQL server design and it would be xtra cool if you could read XML documents straight from T-SQL)
0
 
LVL 2

Expert Comment

by:DaveHavard
ID: 11760208
You could bcp the xml into a single field temporary table using '</emp>' as the row delimiter - this would give you one row. Select this to get your XML variable - not particularly elegant but would work in pure t-sql...

Dave
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 11763128
First a couple of caveats:

1.  The following uses an undocumented SQL Server extended stored procedure (xp_ReadErrorLog).  We are all adults here and we no the risks involved with that.

2.  You will be limited to Xml documents of up to 8,000 bytes in size

Declare      @XmlFilePath varchar(256),
      @ReturnCode integer,
      @XmlText varchar(8000),
      @iDoc integer

Create Table #XmlRows (
      ID integer IDENTITY,
      XmlLine varchar(256),
      ContinuationRow bit)

Set @XmlFilePath = 'G:\emp.XML'                 -- File path relative to SQL Server
Insert #XmlRows (XmlLine, ContinuationRow)
exec @ReturnCode = master..xp_ReadErrorLog 1, @XmlFilePath

Set @XmlText = ''

Select      @XmlText = @XmlText + XmlLine
From      #XmlRows
Order By ID

Drop Table #XmlRows

exec sp_xml_preparedocument @iDoc OUTPUT, @XmlText

Select      *
From      OPENXML(@iDoc, '/emp/employee') WITH(
      [name] varchar(20) 'name',
      phone varchar(20) 'phone',
      email varchar(20) 'email')

exec sp_xml_removedocument @iDoc
0
 
LVL 9

Accepted Solution

by:
SoftEng007 earned 150 total points
ID: 11767795
check this link.

It works fantastic!!
Loads files fast. handles large xml docs and hasn't blown up on me yet!

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20670044.html
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 11767852
Using the sp_OA* functions with the File System object is no better (or faster) than the solution I posted.  If you really want to go this route than use the sp_OA functions with the MSXML object and save your self the trouble of first reading it into a File System Stream object.

Just my 2 cents worth...
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11769579
very true. they should work very similar to each other. I included this url because it does don't have an 8K character limit.
You bring up an interesting point... How could one use MSXML with OPENXML?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 300 total points
ID: 11772828
>>I included this url because it does don't have an 8K character limit.<<
You should not be limited with the sp_OA* functions combined with the MSXML object, either.  But I have never attempted it.

>>How could one use MSXML with OPENXML?<<
OPENXML is in fact using the MSXML dll, but with the advantage of not having to use the sp_OA* functions.  Think of the sp_xml_PrepareDocument as equivalent to the MSXML DOMDocument's LoadXML method and OPENXML somewhat analogous to the SelectNodes method.  What is missing from T-SQL (and I think it unlikely it will be added) is something equal to the MSXML DOMDocument's Load method.
0
 
LVL 9

Expert Comment

by:SoftEng007
ID: 11780241
acperkins,
Thanks for the info. As usual your insight is keen an bright. (i gotta wear shades !;) )
0
 
LVL 1

Author Comment

by:MargusLehiste
ID: 11826168
Sorry but - Ill try to close it when I have figured out the solution in here
 (at the moment Im too occupied with other things)
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

786 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