Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Accessing XML document FROM T-SQL

Posted on 2004-08-08
15
Medium Priority
?
557 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 200 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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 1200 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 600 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 1200 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

886 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