Load xml file by OPENROWSET

In SQL 2000, a long expected feature is to load an xml file and use sp_xml_xx procedures and
OPENXML to shred the data into relational tables. SQL 2005 implemented this feature by the
BULK provider for OPENROWSET.
OPENROWSET supports bulk operations through a built-in BULK provider that enables data from
a file to be read and returned as a rowset.
OPENROWSET ( BULK ‘data_file’ ,
       { FORMATFILE = ‘format_file_path’ [ <bulk_options> ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
)
The format file and bulk options are silimar as BULK INSERT and are not needed if the file
is opened as Blob (e.g. xml document or other type of non-relational documents). So what we
are interested is the Blob options:
SINGLE_BLOB
Returns the contents of data_file as a single-row, single-column rowset of type
varbinary(max). This is the recommend option when you import XML data becuase only
SINGLE_BLOB supports all Windows encoding conversions.After data is imported, we can cast
the result into xml data type
 
SINGLE_CLOB
By reading data_file as ASCII, returns the contents as a single-row, single-column rowset of
type varchar(max), using the collation of the current database.
SINGLE_NCLOB
By reading data_file as UNICODE, returns the contents as a single-row, single-column rowset
of type nvarchar(max), using the collation of the current database.
 
The column name in this case is "BulkColumn"

SET NOCOUNT ON

DECLARE @xml xml

SELECT

@xml=CAST(BulkColumn as xml) FROM OPENROWSET(BULK N‘C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ProcessXMLData Sample\DataFiles\Orders.xml’, SINGLE_BLOB) A

Now the file Orders.xml is loaded into the variable @xml. We can use xquery or OPENXML to handle it:

select

@xml

SELECT

@xml.query(‘/allOrders/orders[1]’)

declare

@i int

exec sp_xml_preparedocument @i OUTPUT,@xml

SELECT

TOP 20 * FROM OPENXML(@i,‘/allOrders/orders’)

WITH

(OrderID int ‘@OrderID’, CustomerID nvarchar(100) ‘@CustomerID’)

exec

sp_xml_removedocument @i

 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s