SQL Server supports the parsing of XML data.
This can be very useful when performing data operations on sets of data. Especially if we want to submit lots of rows to be inserted, updated or deteled.
The Basics of Parsing XML in SQL Server
Before SQL can use XML, it must be converted from text into a usable form. This is done using the sp_xml_preparedocument system stored-procedure. This stored-procedure parses the XML and returns a handle (an int) to the parsed document. I see this as SQL creating a DOM, and the handle is a pointer to it. Hey, it helps me.
This DOM needs removing after use using: sp_xml_removedocument
I don't know for sure, but I imagine bad things would happen if you didn't do this.
declare @xml as varchar(4000)
declare @dom as int
set @xml = '<doc><x id="123" name="first"/><x id="456" name="second"/><x id="789" name="third"/></doc>'
-- parse XML ready for use
exec sp_xml_preparedocument @dom OUTPUT, @xml
-- select XML
select *
from openxml (@dom, '/doc/x') with ([id] int, [name] varchar(32)) as [XML Derived Table]
-- thanks, bye xml
exec sp_xml_removedocument @dom
id name
----------- --------------------------------
123 first
456 second
789 third
(3 row(s) affected)
What's all this varchar(4000) crap?
Using XML in SQL Server is useful, but limiting if we are restricted to 4000 characters of xml. Correct. In T-SQL the maximum length a varchar variable can be is 4000 characters, you cannot have variables of type text. So what do we do? By bundling the functionality in a stored-proc, we can pass the XML to it as unconstrained text. Stored-procedures can have parameters of type text which do not have a length limit, which is ultimately how we'll implement our XML magic.
create procedure usp_xml_example @xml text
as
declare @dom as int
-- parse XML ready for use
exec sp_xml_preparedocument @dom OUTPUT, @xml
-- select from the XML
select *
from openxml (@dom, '/doc/x') with ([id] int, [name] varchar(32)) as [XML Derived Table]
-- thanks, bye xml
exec sp_xml_removedocument @dom
go
-- test procedure
exec usp_xml_example '<doc><x id="123" name="first"/><x id="456" name="second"/><x id="789" name="third"/></doc>'
exec usp_xml_example '<doc><x id="1" name="monkey"/><x id="2" name="piano"/><x id="3" name="tree"/></doc>'
id name
----------- --------------------------------
123 first
456 second
789 third
(3 row(s) affected)
id name
----------- --------------------------------
1 monkey
2 piano
3 tree
(3 row(s) affected)
But What's it Good For?
Here are several variations of the central operation that demostrate how usefull this can be.
Assume we have a table to play with, ExampleTarget
create table ExampleTarget (
[id] int not null,
[data] varchar(32) not null,
constraint PK_ExampleTarget_id primary key ([id])
)
Inserting XML into a Table
insert into ExampleTarget ([id], [data])
select [id], [name]
from openxml (@dom, '/doc/x') with ([id] int, [name] varchar(32)) as [XML Derived Table]
Updating Table with XML
update ExampleTarget
set [data] = [name]
from ExampleTarget
inner join
openxml (@dom, '/doc/x') with ([id] int, [name] varchar(32)) as [XML Derived Table]
on ExampleTarget.[id] = [XML Derived Table].[id]
Deleting from a Table with IDs from XML
delete ExampleTarget
from ExampleTarget
inner join
openxml (@dom, '/doc/x') with ([id] int, [name] varchar(32)) as [XML Derived Table]
on ExampleTarget.[id] = [XML Derived Table].[id]
