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:
1 Comment