RE: how to import an xml file to sql

Ooopsss!

Here's the corrected query:

declare @xml varchar(max)
declare @xmlHandle int

-- Bulk load the xml from the file
set @xml = (
select *
from openrowset
(
bulk 'D:\Temp\Products.xml'
,single_blob
) Products
)

-- Prepare the DOM document
exec sp_xml_preparedocument
@xmlHandle output
,@xml

-- Parse the XML
-- You can include this select statement in your insert statement
select *
from openxml
(
@xmlHandle
,'/Products/Product'
)
with (
id varchar(32) '@Id'
,code varchar(32) 'Code'
,[description] varchar(1024) 'Description'
,url varchar(1024) 'Url'
,orderable varchar(3) 'Orderable'
,taxable varchar(3) 'Taxable'
,[path] varchar(1024) 'Path'
,caption varchar(1024) 'Caption'
)

-- Never forget to remove the DOM document as soon as you're done using it.
exec sp_xml_removedocument
@xmlHandle
go


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/
.

Relevant Pages

  • Re: (OPENXML Or something else ) and sub XmlDocuments?
    ... EXEC sp_xml_prepareDocument @XmlHandle OUTPUT, @XmlDocument ... I am not new to sql, but I am definetely concerning xml in SQL2005, ...
    (microsoft.public.sqlserver.xml)
  • RE: how to import an xml file to sql
    ... -- Bulk load the xml from the file ... @xmlHandle output ... -- Never forget to remove the DOM document as soon as you're done using it. ... Matija Lah, SQL Server MVP ...
    (microsoft.public.sqlserver.datawarehouse)
  • RE: how to import an xml file to sql
    ... I fixed your XML: ... Home Theater Furniture and for all your Home Furniture needs! ... @xmlHandle output ...
    (microsoft.public.sqlserver.datawarehouse)
  • (OPENXML Or something else ) and sub XmlDocuments?
    ... I am not new to sql, but I am definetely concerning xml in SQL2005, ... @XmlDocument XML, ... EXEC sp_xml_prepareDocument @XmlHandle OUTPUT, @XmlDocument ...
    (microsoft.public.sqlserver.xml)
  • OPENXML and subDocuments
    ... I am not new to sql, but I am definetely concerning xml in SQL2005, ... @XmlDocument XML, ... EXEC sp_xml_prepareDocument @XmlHandle OUTPUT, @XmlDocument ...
    (microsoft.public.sqlserver.xml)
Loading