Working with OPENXML in SQL Server
OPENXML is a Transact-SQL keyword that allows you to treat XML data stored in memory as if it were a relational table or view. This functionality is especially helpful when importing or transforming XML content into tabular format.
To use OPENXML, the sp_xml_preparedocument procedure is first called to parse the XML and return a handle to the document, which can then be referenced in SQL queries.
Example: Populating Tables Using OPENXML
-- Create necessary tables
CREATE TABLE Customers (
CustomerID varchar(20) PRIMARY KEY,
ContactName varchar(20),
CompanyName varchar(20)
);
CREATE TABLE Orders (
CustomerID varchar(20),
OrderDate datetime
);
-- Declare XML and handle
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max);
-- Set XML data
SET @xmlDocument = N'
<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00" />
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00" />
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">
No Orders yet!
</Customers>
</ROOT>';
-- Prepare the XML document
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Insert customers
INSERT INTO Customers
SELECT * FROM OPENXML(@docHandle, '/ROOT/Customers') WITH Customers;
-- Insert orders
INSERT INTO Orders
SELECT * FROM OPENXML(@docHandle, '//Orders') WITH Orders;
-- Query with OPENXML
SELECT * FROM OPENXML(@docHandle, '/ROOT/Customers/Orders')
WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Clean up the XML handle
EXEC sp_xml_removedocument @docHandle;
Example: Using nodes() Method
SELECT
nref.value('(first-name/text())[1]', 'nvarchar(50)') AS FirstName,
nref.value('(last-name/text())[1]', 'nvarchar(50)') AS LastName
FROM T
CROSS APPLY xCol.nodes('//author') AS R(nref)
WHERE nref.exist('first-name[. != "David"]') = 1;
Looping Through XML Using OPENXML()
DECLARE name_cursor CURSOR FOR
SELECT xCol FROM T;
OPEN name_cursor;
DECLARE @xmlVal XML;
DECLARE @idoc int;
FETCH NEXT FROM name_cursor INTO @xmlVal;
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal;
SELECT *
FROM OPENXML(@idoc, '//author')
WITH (
FirstName varchar(50) 'first-name',
LastName varchar(50) 'last-name'
) R
WHERE R.FirstName != 'David';
EXEC sp_xml_removedocument @idoc;
FETCH NEXT FROM name_cursor INTO @xmlVal;
END
CLOSE name_cursor;
DEALLOCATE name_cursor;
Example XML Structure
<Customers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Customer>
<ItemId>1001</ItemId>
<Value>Mr Patel</Value>
</Customer>
<Customer>
<ItemId>2002</ItemId>
<Value>Mr Bhatt</Value>
</Customer>
</Customers>
Extracting Data from XML
SELECT
Cst.value('(ItemId)[1]', 'int') AS ItemID,
Cst.value('(Value)[1]', 'Varchar(50)') AS CustomerName
FROM dbo.Sales.CustomerList.nodes('/Customers/Customer') AS A(Cst);
Using CROSS APPLY with XML
CREATE TABLE Sales2020 (ID int, CustomerList xml);
INSERT INTO Sales2020
SELECT 1, '
<Customers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Customer>
<ItemId>1001</ItemId>
<Value>Mr Patel</Value>
</Customer>
<Customer>
<ItemId>2</ItemId>
<Value>Mr Bhatt</Value>
</Customer>
</Customers>';
SELECT
N.C.value('ItemId[1]', 'int') AS ItemId,
N.C.value('Value[1]', 'varchar(100)') AS Value
FROM Sales2020
CROSS APPLY CustomerList.nodes('//Customer') AS N(C);
