SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Importing XML
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

rda
Starting Member

2 Posts

Posted - 09/01/2004 :  12:52:45  Show Profile
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

Slovenia
11751 Posts

Posted - 09/01/2004 :  13:01:04  Show Profile  Visit spirit1's Homepage
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 :)
Go to Top of Page

rda
Starting Member

2 Posts

Posted - 09/01/2004 :  13:35:21  Show Profile
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?
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/01/2004 :  13:42:24  Show Profile
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-)
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 09/01/2004 :  14:13:08  Show Profile
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 */
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/01/2004 :  14:44:53  Show Profile
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-)
Go to Top of Page

Sitka
Aged Yak Warrior

USA
571 Posts

Posted - 09/01/2004 :  16:51:22  Show Profile
word

I wish someone would start an Official XML Rant Thread.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/01/2004 :  17:01:47  Show Profile
Wish granted

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


Brett

8-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000