OPENXML

From: Mark (Mark_at_nowhere.com)
Date: 11/09/04 

Date: Tue, 9 Nov 2004 16:21:12 -0600

Is there a way to use read data from an XML file, and insert it into a
table? The code example below is from BOL using OPENXML, but doesn't call
to a file directly.

Thanks in advance.

Mark

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT,
      N'<ROOT>
         <Customers CustomerID="XYZAA" ContactName="Joe"
               CompanyName="Company1">
            <Orders CustomerID="XYZAA"
               OrderDate="2000-08-25T00:00:00"/>
            <Orders CustomerID="XYZAA"
               OrderDate="2000-10-03T00:00:00"/>
         </Customers>
         <Customers CustomerID="XYZBB" ContactName="Steve"
               CompanyName="Company2">No Orders yet!
         </Customers>
      </ROOT>'
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@hDoc, N'/ROOT/Customers')
     WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@hDoc, N'//Orders')
     WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@hDoc, N'/ROOT/Customers/Orders') with (CustomerID
nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @hDoc

Relevant Pages

  • RE: OPENXML
    ... EXEC sp_xml_preparedocument @hDoc OUTPUT, @InputXML ... -- Use OPENXML to provide rowset consisting of customer data. ... INSERT Customers ...
    (microsoft.public.sqlserver.programming)
  • RE: Extracting XML data to columns during query
    ... This is reading from an external XML file. ... create table Customers primary key, ... -- Use OpenXML to provide rowset consisting of customer data. ...
    (microsoft.public.sqlserver.xml)
  • Problem with OPENXML
    ... I'm trying to open an XML file with OPENXML. ... The error description is 'The following tags were not closed: gpx, rte, ... WITH (lat float, ...
    (microsoft.public.sqlserver.xml)
  • RE: insert into XML table - datetime type question
    ... exec sp_xml_preparedocument @hDoc OUTPUT,@Empdata ... > "RobKaratzas" wrote: ... >> can anyone give me some good links/examples on datetime datatypes and openxml? ...
    (microsoft.public.sqlserver.xml)
  • Re: JDBC vs Hibernate
    ... I read somewhere that OPENXML is faster, (I have the XML file in memory already, my application is generating an XML file) ... "INSERT DataSource SELECT * FROM OPENXML(@_hDoc, ...
    (comp.lang.java.databases)