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:  

VN:F [1.9.22_1171]
Rating: 9.1/10 (12 votes cast)
VN:F [1.9.22_1171]
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!