Using Metaproperties in OpenXML (13308 Requests)

Goal: Using the following XML, insert three node records in the node table and then three two subnode records into the subnode table with a reference to the correct parent node. The node table uses a GUID as the node key.

 
<nodes>
  <node attr="123">
    <subnode val="789" />
    <subnode val="456" />
  </node>
  <node attr="123" />
  <node attr="321" />
</nodes>
 

If the attr values were unique then we could do a simple join on two OpenXML tables. However, since the attr values are not unique this cannot be done. The following SQL code demonstrates how this can be done. The code will be explained below.

 
declare @i int
 
-- prepare the document
exec sp_xml_preparedocument @i output, '
<nodes>
  <node attr="123">
    <subnode val="789" />
    <subnode val="456" />
  </node>
  <node attr="123" />
  <node attr="321" />
</nodes>'
 
-- create a table variable to store values
declare @t table(
 nodeid uniqueidentifier,
 oxid int, 
 attr int)
 
-- fill the table variable
insert into @t
select  newid(), id, attr
from  OpenXml(@i, 'nodes/node', 9) with (attr int, id int '@mp:id')
 
-- insert into our node table
-- insert into nodetable select nodeid, attr from @t
 
-- insert into our subnode table
-- insert into subnodetable
select  t.nodeid, ox.val
from  @t t,
 OpenXml(@i, 'nodes/node/subnode', 9) with (attr int '../@attr', val int, parentid int '@mp:parentid') ox
where t.oxid = ox.parentid
 
-- remove the xml document
exec sp_xml_removedocument @i
 

The code above makes use of the xml metaproperties that are available when using OpenXML. These properties are documented here.

In the first select statement we get the node id value using the @mp:id which gives us the unique id for each node element. Then in the next query we can get the @mp:parentid of the subnode which points to the @mp:id of the node. This allows us to figure out which node the subnode belongs to without needing a unique value in the node itself.

Feedback

# oqflkup rkeqxotzd 8/29/2007 10:36 AM oiae@mail.com

fzvod jwxftvs pwmgkn txquhcapv kaqxcsu cpxnrzjv fklospi

# gtfpau rkgtv 10/3/2007 7:31 PM lfsdqora@mail.com

cferh zulvkjia ovnzfe brpx psqoubw nkfoi gsce

# re: Using Metaproperties in OpenXML 11/27/2007 9:32 AM spjbiszywf

Hello! Good Site! Thanks you! psqfpjtaqvp