OPENXML and namespace

When xml document has namespace, there is extra considerations to use OPENXML to get data from it.
 
Handling one namespace:
declare @xmlDoc nvarchar(4000)
SET @xmlDoc=’
<ObjectList xmlns="http://www.my.com/sample">
 <Version>1.0.0</Version>
 <ObjectOption>1</ObjectOption>
 <Object ObjectID="./2.3/2.3" IncludeDescendents="4"/>
 <Object ObjectID="./3.4/3.4" IncludeDescendents="1"/>
 <Object ObjectID="./2.8/2.8" IncludeDescendents="2"/>
</ObjectList>’
 
declare @i int
exec sp_xml_preparedocument @i output, @xmlDoc,'<root xmlns:sample="http://www.my.com/sample"/>’
 
SELECT * FROM OPENXML(@i, ‘//sample:ObjectList/sample:Object’,1)
 WITH (ObjectID       nvarchar(200)         ‘@ObjectID’,
  IncludeDescendents int      ‘@IncludeDescendents’)
 
SELECT * FROM OPENXML(@i, ‘//sample:ObjectList’,2)
 WITH ([sample:ObjectOption]      int, [sample:Version] nvarchar(50))
exec sp_xml_removedocument @i
 
1) preare the xml document:
We must pass the third parameter to sp_xml_preparedocument. It tells the parser the namespace and it’s alias. But it must be wrapped by "root " element, regardless what root element you have (ObjectList in our sample) in your document. 
 
2) Read data from elements:
When using OPENXML to read element data from xml, in the xpath, all element must be prefixed by the alias of the namespace, inlcuding the elemnts you are reading, which results in the column names of the output has the format [alias:element name]
 
3) Read data from attribute:
Except the xpath has namespace alias, the syntax is the same with no namesapce xml.
 
 
 
Handling multiple namespace:
We need to list all namespaces in the xml document when calling sp_xml_preparedocument. The following xml has two namespaces: "http://www.my.com/sample" xmlns:S2="http://www.my.com/sample2"
 
declare @xmlDoc nvarchar(4000)
SET @xmlDoc=’
<ObjectList xmlns="http://www.my.com/sample" xmlns:S2="http://www.my.com/sample2" >
 <Version>1.0.0</Version>
 <S2:ObjectOption>1</S2:ObjectOption>
 <S2:Object ID="./2.3/2.3" Name="test1"/>
 <S2:Object ID="./2.3/2.3" Name="test2"/>
 <Object ObjectID="./3.4/3.4" IncludeDescendents="1"/>
 <Object ObjectID="./2.8/2.8" IncludeDescendents="2"/>
</ObjectList>’
declare @i int
exec sp_xml_preparedocument @i output, @xmlDoc,'<root xmlns:sample="http://www.my.com/sample" xmlns:S2="http://www.my.com/sample2" />’
SELECT * FROM OPENXML(@i, ‘//sample:ObjectList/sample:Object’,1)
 WITH (ObjectID       nvarchar(200)         ‘@ObjectID’,
  IncludeDescendents int      ‘@IncludeDescendents’)
SELECT * FROM OPENXML(@i, ‘//sample:ObjectList/S2:Object’,1)
 WITH (ObjectID       nvarchar(200)         ‘@ID’,
  ObjectName nvarchar(50)      ‘@Name’)
SELECT * FROM OPENXML(@i, ‘//sample:ObjectList’,2)
 WITH ([S2:ObjectOption]      int, [sample:Version] nvarchar(50))
exec sp_xml_removedocument @i
 
 
 
 
Advertisements
This entry was posted in SQL server. Bookmark the permalink.

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