openXML command - 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

SQL Server 2000 Forums

Transact-SQL (2000)

openXML command

Author

Topic

avgoustinosc
Starting Member

17 Posts

Posted - 2006-11-06 : 05:42:27

How i can use openxml command in T-SQL in order to import from an xml file?

Any help will be appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 05:45:53

Let's see what Books Online says.

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

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-06 : 06:06:48

Here is one example which you may find of use:

[url]http://www.builderau.com.au/program/sqlserver/soa/Reading_an_XML_file_from_SQL_Server/0,339028455,339174685,00.htm[/url]

Harsh Athalye
India.
"Nothing is Impossible"

avgoustinosc
Starting Member

17 Posts

Posted - 2006-11-06 : 08:30:35

Thanks for your reply guys, but i already found these think in the internet. My problem is that my file is very big (about 3000 records) and i cannot use these procedures you recommend. I need to find a way to import directly my file into the database.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 08:50:52

3000 records only? That is not very big.
What are the error messages you get?

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-11-06 : 09:03:03

if you're using sql server 2005 you could use bulk insert into the xml datatype column.

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