TechIt · Windows

Using metaproperties in Ms Sql Server when xml processing in sql with OpenXml

In processing some XML into an SqlServer db it can often be useful to have the element name or the parent element name output, depending on how your xml data is organised.

Below is an example of using meta properties in MS SQL Server for just this purpose.

DECLARE @XmlDocumentHandle int

set @RESPONSE = '

desc 1

desc 2


PRINT '**DEBUG ** : Preparing xml doc for response.'
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @RESPONSE

select *
from OPENXML (@XmlDocumentHandle, '/frag/foos/*/bar', 2)
whichFoo varchar(50) '@mp:parentlocalname',
whichBar varchar(50) '@mp:localname',
bar_name varchar(50) 'name',
bar_desc varchar(50) 'desc'
) AS foobars

-- Remove the internal representation.
EXEC sp_xml_removedocument @XmlDocumentHandle


whichFoo WhichBar bar_name bar_desc
foo1 bar name1 desc 1
foo2 bar name2 desc 2

It is worth noting perhaps that these to meta properties can only be used in the context of the root element as specified by the path in the Openxml function call.

You can’t for instance retrieve the value of “whichBar” from the above example by doing something like

nameParent varchar(50) 'name/@mp:parentlocalname'

In my case I just altered the xml to have a different/better(?) structure, but still in future I’m sure I will find a use for this.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s