OPENXML and Namespaces

Friday 30, August 2019 | Post link

JSON shmason.. XML is still cool. Parsing XML, running xpaths is usually quicky done but I always seem to forget how to call the Xml APIs when dealing with namespaces. This post captures namespace usage from SQL Server sp_xml_preparedocument point of view.

A simple XML and converting to rowset

declare @xmlWithoutNs nvarchar(max) = N'
<addressBook>
	<entries>
		<entry name="Siddharth Barman" email="siddharthbarman@email.com" />
		<entry name="Sajan Kumar" email="sajankumar@email.com" />
	</entries>
</addressBook>';

declare @idoc int

exec sp_xml_preparedocument @idoc output, @xmlWithoutNs;
select * from OPENXML(@idoc, N'/addressBook/entries/entry') 
with
(
	[name]  varchar(50),
	[email] varchar(50)
);
exec sp_xml_removedocument @idoc;

The result:

Let's now add two XML namespaces into the XML and try the same query.

declare @xmlWithNs nvarchar(max) = N'
<addressBook xmlns="http://www.tempuri.org/addressBook.xsd">
    <entries xmlns:e="http://www.tempuri.org/addressBookProperties.xsd">
        <entry e:name="Siddharth Barman" e:email="siddharthbarman@email.com" />
        <entry e:name="Sajan Kumar" e:email="sajankumar@email.com" />
    </entries>
</addressBook>';

declare @idoc int;
exec sp_xml_preparedocument @idoc output, @xmlWithNs 

select * from OPENXML(@idoc, N'/addressBook/entries/entry') 
with
(
    [name]  varchar(50),
    [email] varchar(50)
)
exec sp_xml_removedocument @idoc;

The result:

OK, so we didn't get anyhting and it's because we have to take into account the xml namespaces that have been specified in the XML.

The correct way to deal with namespace.

declare @xmlWithNs nvarchar(max) = N'
<addressBook xmlns="http://www.tempuri.org/addressBook.xsd">
    <entries xmlns:e="http://www.tempuri.org/addressBookProperties.xsd">
        <entry e:name="Siddharth Barman" e:email="siddharthbarman@email.com" />
        <entry e:name="Sajan Kumar" e:email="sajankumar@email.com" />
    </entries>
</addressBook>';

declare @idoc int;
exec sp_xml_preparedocument @idoc output, @xmlWithNs, N'<root xmlns:d="http://www.tempuri.org/addressBook.xsd" xmlns:e="http://www.tempuri.org/addressBookProperties.xsd"/>' ;

select * from OPENXML(@idoc, N'/d:addressBook/d:entries/d:entry') 
with
(
    [e:name]  varchar(50),
    [e:email] varchar(50)
);
exec sp_xml_removedocument @idoc;

The result:

XML with namespace parsing result correctly

What we did was mention the namespaces both default (xmlns="http..") and the explicit one (xmlns:e="http..") when calling the sp_xml_preparedocument and use these when specifying the XPATH in OPENXML function.

A slightly different way to do the same thing is given below. Here we are renaming the columns when converting the XML to rowset and also using the node text.

declare @xmlWithNs nvarchar(max) = N'
<addressBook xmlns="http://www.tempuri.org/addressBook.xsd">
    <entries xmlns:e="http://www.tempuri.org/addressBookProperties.xsd">
        <entry e:id="1" e:email="siddharthbarman@email.com">Siddharth Barman</entry>
        <entry e:id="2" e:email="sajankumar@email.com">Sajan Kumar</entry>
    </entries>
</addressBook>';

declare @idoc int;
exec sp_xml_preparedocument @idoc output, @xmlWithNs, N'' ;

select * from OPENXML(@idoc, N'/d:addressBook/d:entries/d:entry') 
with
(
    ID int '@e:id',
    FullName varchar(50) 'text()',
    EmailID varchar(50) '@e:email'
);
exec sp_xml_removedocument @idoc;

The result:

Get the Code

Categories: Programming (28) SQL (2) XML (2)

Tags: OPENXML(1) SQL(2)

Comments