| Author: |
-- Managing Editor, perfectxml.com |
| Article Date: |
August 2001 |
| Abstract: |
With SQL Server 2000, there are many ways you can get XML representation of relational data. The options include using FOR XML clause with SELECT statement, ADO Recordset's Save method with adPersistXML parameter, etc. It would be nice to have a reverse mechanism, which allows turning XML document into tabular, relational rowset format. The good news is that SQL Server 2000 team thought about this and introduced a new Transact-SQL keyword named OPENXML along with two new supporting system stored procedures: sp_xml_preparedocument and sp_xml_removedocument.
OPENXML allows accessing XML data as if it were a relational recordset. It provides a tabular (rowset) view of in-memory representation of an XML document. Technically, OPENXML is a rowset provider similar to a table or a view; hence it can be used wherever a table or a view is used. For instance, you can use OPENXML with SELECT or SELECT INTO statements, to work on an XML document that is retained in memory.
In this article, we're going to take a detailed look at OPENXML syntax and write couple of examples. Since use of OPENXML is dependent on sp_xml_preparedocument and sp_xml_removedocument system stored procedures, we'll study those first. |
| Related Articles: |
Importing XML into SQL Server 2000
Exporting SQL Data as XML
|
If you have any questions or comments, feel free to contact author of this article, Darshan Singh at .
Related KB Articles:
- HOW TO: Perform Bulk Updates and Inserts Using OpenXML with .NET Providers in Visual C# .NET
- HOW TO: Perform Bulk Updates and Inserts Using OpenXML with .NET Providers in Visual C++ .NET
- PRB: Limitations of OpenXML in SQL Server 2000
- PRB: sp_xml_preparedocument Stored Procedure Fails with Error 6603
- SAMPLE: How To Retrieve Relational Data Using OpenXML with ADO in Visual C++
- HOWTO: Retrieve Relational Data with OpenXML in an ASP Client
- PRB: Access Violation Occurs with the Use of OpenXML in a User-Defined Function
Recently asked questions on this article:
Q: I would like to dynamically generate the OPENXML statement and pass it the selection XPath expression and the select column list. Can you please show an example of how to do it?
A: There are I think few ways to dynamically build and execute T-SQL statement. Here is an example that makes use of sp_executesql stored procedure to execute a dynamically generated T-SQL statement.
--Source XML Document
DECLARE @doc nvarchar (4000)
SET @doc ='<empDetails>
<emp id="1" firstName="John" lastName="smith"/>
<emp id="2" firstName="Mark" lastName="Robinson"/>
<emp id="3" firstName="Dave" lastName="Schur"/>
</empDetails>'
--Load XML document
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
--Dynamically generated SQL statement
DECLARE @sqlStatement nvarchar(4000)
--Our main params that we would like to pass/control
DECLARE @primaryXPath nvarchar(255)
DECLARE @selectFieldList varchar(2000)
--First Test
SET @primaryXPath = '/empDetails/emp'
SET @selectFieldList = '[FirstName] NVARCHAR(100) ''@firstName'''
SET @sqlStatement = 'SELECT * FROM OPENXML (@idoc, @primaryXPath, 3)
WITH
(' + @selectFieldList + ')'
EXECUTE sp_executesql @sqlStatement,
@params = N'@idoc INT, @primaryXPath nvarchar(255), @selectFieldList varchar(2000)',
@idoc=@idoc, @primaryXPath=@primaryXPath, @selectFieldList=@selectFieldList
--Second Test
SET @primaryXPath = '/empDetails/emp[@id=3]'
SET @selectFieldList='[FirstName] NVARCHAR(100) ''@firstName'', [LastName] NVARCHAR(100) ''@lastName'''
SET @sqlStatement = 'SELECT * FROM OPENXML (@idoc, @primaryXPath, 3)
WITH
(' + @selectFieldList + ')'
EXECUTE sp_executesql @sqlStatement,
@params = N'@idoc INT, @primaryXPath nvarchar(255), @selectFieldList varchar(2000)',
@idoc=@idoc, @primaryXPath=@primaryXPath, @selectFieldList=@selectFieldList
--Unload the document
EXEC sp_xml_removedocument @idoc
The first test above produces three rows with firstName value; and the second test returns first and last name for employee with id = 3.
Q: I have an XML document that uses Namespaces, when I try to use this document with OPENXML, it does not work. I am not able to get the node values.
A: The answer lies in the fact that sp_xml_preparedocument takes an optional third parameter where we can specify the namespace to be used with the XPath expressions passed to the OPENXML. The use of prefixes in the namespace declaration with sp_xml_preparedocument is mandatory, even if the XML document uses the default namespace.
In the following example, the XML document uses the namespace declarations (default and with prefix). The first OPENXML illustrates accessing the nodes defined with namespace prefix; and the second OPENXML illustrates accessing the nodes defined with default namespace.
DECLARE @idoc int
DECLARE @doc varchar (1000)
SET @doc ='
<?xml version="1.0" encoding="UTF-8"?>
<bk:Books xmlns:bk="https://perfectxml.com">
<bk:Book bk:ISBN="186100589X">
<bk:Title>XML Application Development with MSXML 4.0</bk:Title>
<bk:Publisher>Wrox Press</bk:Publisher>
<bk:DateOfPurchase>2/1/2002</bk:DateOfPurchase>
<Stores xmlns="BooksURI">
Amazon, B&N
</Stores>
</bk:Book>
<bk:Book bk:ISBN="1861005466">
<bk:Title>Professional SQL Server 2000 XML</bk:Title>
<bk:Publisher>Wrox Press</bk:Publisher>
<bk:DateOfPurchase>9/10/2001</bk:DateOfPurchase>
<Stores xmlns="BooksURI">
BookPool
</Stores>
</bk:Book>
</bk:Books>
'
--Create an internal representation of the XML document
-- Example 1: XML doc uses the Namespace prefix
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc,
'<bk:Books xmlns:bk="https://perfectxml.com" />'
-- Execute a SELECT statement that uses the OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/bk:Books/bk:Book', 8)
WITH
(
ISBN varchar(100) '@bk:ISBN',
Title varchar(100) 'bk:Title',
Publisher varchar(100) 'bk:Publisher',
DateOfPurchase varchar(100) 'bk:DateOfPurchase'
)
-- Clear the XML document from memory
EXEC sp_xml_removedocument @idoc
---------------------------------------
--Create an internal representation of the XML document
-- Example 2: XML Node uses the Default Namespace
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc,
'<bk:Books xmlns:st="BooksURI" xmlns:bk="https://perfectxml.com"/>'
-- Execute a SELECT statement that uses the OPENXML rowset provider
SELECT *
FROM OPENXML (@idoc, '/bk:Books/bk:Book', 8)
WITH
(
Stores varchar(100) 'st:Stores'
)
-- Clear the XML document from memory
EXEC sp_xml_removedocument @idoc
|
In the above example, the XML document contains Books node and its child nodes defined using a namespace prefix, while the Stores node uses the default namespace.
Note how we have defined the namespace with the sp_xml_preparedocument; even for the default namespace node in the XML document, we have defined the prefix while declaring the namespace with sp_xml_preparedocument.
Q: How do I update using OPENXML and is it required to match column names with attribute/element names? Thanks.
Answer:
A: Here is an example of updating a record using OPENXML
USE tempdb
go
CREATE TABLE emps (empid INT IDENTITY(1,1), empname VARCHAR(30))
go
INSERT INTO emps SELECT 'Darshan'
go
INSERT INTO emps SELECT 'Sunny'
go
INSERT INTO emps SELECT 'Andy'
go
SELECT * FROM emps
go
-----------Updating using OPENXML
DECLARE @hDoc int
DECLARE @cDoc varchar(8000)
SET @cDOC = '<ROOT><NewName ID="3">ASmith</NewName></ROOT>'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @cDoc
UPDATE emps
SET empname = x.NewName
FROM
(
SELECT NewName, empid
FROM OPENXML(@hDOC, '/ROOT', 1)
WITH
(
NewName varchar(30) '/ROOT/NewName',
empid int '/ROOT/NewName/@ID'
)
) x, emps
WHERE emps.empid = x.empid
EXEC sp_xml_removedocument @hdoc
go
SELECT * FROM emps
go
B.) I don't think element/attribute names should necessarily match column names (infact above example proves that! ).
Here is another small example of inserting data into a table from the XML document:
DROP TABLE emps
go
CREATE TABLE emps (ID int, EmpName varchar(30))
GO
DECLARE @hDoc int
DECLARE @cDoc varchar(8000)
SET @cDOC = '<ROOT><NewName ID="3">ASmith</NewName><NewName ID="4">BOB</NewName></ROOT>'
EXEC sp_xml_preparedocument @hDoc OUTPUT, @cDoc
INSERT INTO emps
SELECT *
FROM OPENXML(@hDOC, '/ROOT/NewName', 3)
WITH
(
ID int '@ID',
NewName varchar(30) '.'
)
EXEC sp_xml_removedocument @hdoc
go
SELECT * FROM emps
go
Back to Articles Page
All information on this site is for training only. We do not warrant its correctness or its fitness to be used. The risk of using it remains entirely with the user.
|
