swirl
Home Software Blog Wallpapers Webtools
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:

Simple XML parsing 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:

XML with namespace parsing 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:

XML with namespace parsing result correctly

Get the Code



Comments

Posts By Year

2019 (5)
2018 (8)
2017 (11)
2016 (6)
2015 (18)
2014 (2)
2013 (4)
2012 (2)

Posts By Category

.NET (1)
.NET Core (2)
ASP.NET MVC (4)
AWS API Gateway (1)
Android (1)
Azure (2)
Book review (3)
Business (1)
C# (3)
C++ (1)
Containers (3)
Corporate culture (1)
Database (1)
Database migration (1)
Desktop (1)
DotNet (2)
Entity Framework (3)
Git (2)
IIS (1)
Java (2)
Life (6)
Linux (1)
Lucene (1)
OData (1)
Office (1)
PHP (1)
PowerShell (2)
Programming (21)
Rants (5)
SQL (1)
Software Engineering (1)
Software development (1)
Solr (1)
Sql Server (2)
Storage (1)
T-SQL (1)
TDD (1)
TSQL (5)
Tablet (1)
Technology (1)
Test Driven (1)
Unit Testing (1)
Utilities (2)
VC++ (1)
Visual Studio (2)
Wallpapers (1)
Windows (8)
XML (2)

Posts By Tags

.NET(3) API Gateway(1) ASP.NET(4) AWS(1) Adults(1) Advertising(1) Android(1) Anti-forgery(1) Azure(2) Backup(1) Beliefs(1) Book review(2) Books(1) Busy(1) C#(3) C++(3) CLR(1) CORS(1) CSRF(1) CTE(1) Callbacks(1) Checkbox(1) Cmdlet(1) Commons(1) Company culture(1) Consumerism(1) Containers(3) Core(2) DPI(1) Data-time(1) Database(1) Debugging(1) Delegates(1) Developer(2) Dockers(2) EF 1.0(1) Entity framework(1) Events(1) File copy(1) File history(1) Font(1) Git(2) GradleApache(1) HierarchyID(1) IIS(1) Installing(1) Intelli J(1) JSON(1) JavaScript(1) Linux(1) Localization(1) Log4J(1) Lucene(1) MVC(4) Management(2) Migration history(1) Mobile Apps(1) Modern Life(1) Money(1) OData(1) OPENXML(1) Objects(1) Office(1) Organization(1) PHP(1) Paths(1) PowerShell(2) Programming(1) Python(1) Quality(1) Runtimes(1) SD card(1) SQL(2) SQL Code-first Migration(1) SSH(1) Sattelite assemblies(1) School(1) Self reliance(1) Shell(1) Solr(1) Sony VAIO(1) Spirituality(1) Sql Express(1) System Image(1) TDD(1) TSQL(3) Table variables(1) Tables(1) Tablet(1) Url rewrite(1) Validation(2) Wallpaper(1) Wallpapers(1) Web Development(4) Windows(2) Windows 10(1) Windows 2016(2) Windows 8.1(1) Work culture(1) XML(1) Yii(1)