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 @RESPONSE XML
DECLARE @XmlDocumentHandle int

set @RESPONSE = '

name1
desc 1

name2
desc 2

'

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

select *
from OPENXML (@XmlDocumentHandle, '/frag/foos/*/bar', 2)
WITH (
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

Results

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.

Ref: http://msdn.microsoft.com/en-us/library/ms178088.aspx

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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