SQLXML : How to Bulk Load XML From a File Using OPENXML

Using OPENXML you can import XML documents into an XML variable, or into a table that has XML columns. The following is an example:

   1: -- ==========================================================================

   2: -- Object       : OPENXML1.sql

   3: -- Object Type  : Script

   4: -- Description  : Various examples

   5: -- Developer    : Donabel Santos

   6: -- Origin       : 2008/08/17

   7: -- Last Modified: 2008/10/04

   8: -- Notes        :

   9: -- ==========================================================================

10:

  11: -- bulk load

  12: -- For this example, XML file must be saved in C:

  13: -- XML file also should specify UTF-8 encoding, ie:

  14: -- <?xml version="1.0" encoding="UTF-8"?>

15:

  16: DECLARE @XMLTable TABLE

  17: (

  18:    xmlcol XML

  19: )

20:

  21: INSERT INTO @XMLTable(xmlcol)

  22: SELECT

  23:     InvoicesXML

  24: FROM

  25: (

  26:     SELECT *

  27:     FROM OPENROWSET(BULK 'c:invoice.xml',SINGLE_BLOB) AS Invoices

  28: ) AS Invoices(InvoicesXML)

29:

  30: SELECT *

  31: FROM @XMLTable

32:

33:

Rating: 9.1/10 (12 votes cast)

Rating: +1 (from 1 vote)

SQLXML : How to Bulk Load XML From a File Using OPENXML, 9.1 out of 10 based on 12 ratings

Be Sociable, Share!

belle’s sql musings