Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

Accessing XML document FROM T-SQL

How can I access an .xml FILE from t-sql (query analyzer) ?

(I need an example)

0
MargusLehiste
Asked:
MargusLehiste
  • 6
  • 4
  • 3
  • +1
4 Solutions
 
DaveHavardCommented:
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
 
MargusLehisteAuthor Commented:
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
 
Anthony PerkinsCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
MargusLehisteAuthor Commented:
so how would i be able to accomplish what i want - have an XML document and retrieve a SQL result ?
0
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
So do you want to make this a T-SQL only solution, even though it is not recommended?
0
 
MargusLehisteAuthor Commented:
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
 
DaveHavardCommented:
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
 
Anthony PerkinsCommented:
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
 
SoftEng007Commented:
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
 
Anthony PerkinsCommented:
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
 
SoftEng007Commented:
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
 
Anthony PerkinsCommented:
>>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
 
SoftEng007Commented:
acperkins,
Thanks for the info. As usual your insight is keen an bright. (i gotta wear shades !;) )
0
 
MargusLehisteAuthor Commented:
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
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now