OPENXML in TSQL

Today I'm working on a stored procedure in SQL Server 2000. I need to pass in an xml document inside the string property of a certain object. It took me a while to figure out how to use the OPENXML command to shred the fields out of the xml that was passed in the string.
Now that I've played around with the OPENXML command, I thought I'd post the sample script that I used to figure it out here, for posterity's sake, ya know.

DECLARE @xint INT
DECLARE @XML VARCHAR (200)
SET @XML = '
<xdoc>
<subobject myattrib="22">
<myelem>something</myelem>
<mycomplexdeal myid="42">wow</mycomplexdeal>
</subobject>
</xdoc>'

--Get a handle on the document.
exec sp_xml_preparedocument @xint output, @XML
select * from openxml(@xint, 'xdoc/subobject',8)
with([myattrib] int '@myattrib',
[myelem] varchar(30) 'myelem',
[renamedfield] varchar(30) 'myelem',
[mycomplexdeal] varchar(30) 'mycomplexdeal',
[myid] int 'mycomplexdeal/@myid',
[nothinghere] varchar(30))
--Release the handle on the document

exec sp_xml_removedocument @xint

I consulted MSDNs library onMSDN's OPENXML to figure most of it out.

Comments

Popular Posts