OPENXML, a Transact-SQL keyword, provides a rowset over in-memory XML documents that is similar to a table or a view.
sp_xml_preparedocument. This parses the XML document and returns a handle to the parsed document that is ready for consumption.
-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
ContactName varchar(20),
CompanyName varchar(20));
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
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>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers
SELECT *
FROM OPENXML(@docHandle, N'/ROOT/Customers')
WITH Customers;
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders
SELECT *
FROM OPENXML(@docHandle, N'//Orders')
WITH Orders;
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders')
WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle;
Example: Using nodes()
SELECT nref.value('(first-name/text())[1]', 'nvarchar(50)') FirstName,
nref.value('(last-name/text())[1]', 'nvarchar(50)') LastName
FROM T CROSS APPLY xCol.nodes('//author') AS R(nref)
WHERE nref.exist('first-name[. != "David"]') = 1
Example: Using OpenXml() on the xml Data Type
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
<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>
SELECT
Cst.value('(ItemId)[1]', 'int') AS 'ItemID',
Cst.value('(Value)[1]', 'Varchar(50)') AS 'Customer Name'
FROM
dbo.Sales.CustomerList.nodes('/Customers/Customer') AS A(Cst)
Using Cross Apply
create table Sales2020 (ID int ,CustomerlList xml)
insert sales 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') ItemId,
N.C.value('Value[1]', 'varchar(100)') Value
FROM Sales2020
CROSS APPLY CustomerList.nodes('//Customer') N(C)
References:
