Importing XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Importing XML

Author

Topic

rda
Starting Member

2 Posts

Posted - 2004-09-01 : 12:52:45

Apparently I can't just use OPENXML to grab XML files residing say, on a network drive. Is this an easy task? Any code samples?

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 13:01:04

loot in BOL.
this is directly from there:


DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
   </Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
   </Order>
</Customer>
</ROOT>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

Here is the result set: 

CustomerID ContactName          
---------- -------------------- 
VINET      Paul Henriot
LILAS      Carlos Gonzlez

Go with the flow & have fun! Else fight the flow :)

rda
Starting Member

2 Posts

Posted - 2004-09-01 : 13:35:21

it looks like I set a handle to the doc, @hdoc and a variable @doc. Is it as simple as putting a path in @doc like c:\myxml\? I'm sure its not but maybe?

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 13:42:24

I think you have to put the data in to a variable...

I don't think I've ever seen a file assigned to a local variable before...

You might have to load it to a table...

Did I mention I hate XML

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-01 : 14:13:08

short xml discussion at bottom of this thread
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39181

Xtreme Muckup Language

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 14:44:53

Let's take the nice relational model and add to it hierarchical "capabilities" But let's make sure the data is stored in a text column, so they can get it out in chuncks, so they can the use these "tools" to parse it out into relational format, where it can be stored, and more easily accessed....

Did I mention I hate XML

Brett

8-)

Sitka
Aged Yak Warrior

571 Posts

Posted - 2004-09-01 : 16:51:22

word

I wish someone would start an Official XML Rant Thread.

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-01 : 17:01:47

Wish granted

[url]http://weblogs.sqlteam.com/brettk/archive/2004/09/01/1999.aspx[/url]

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources