Getting NULL in sql table while parsing xml in SQL Server 2008

Question

I am trying to parse an XML document with a query.

Here is an example of my XML:

<export xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://bbhgtm.gov.com/oos/export/1" xmlns:oos="http://bbhgtm.gov.com/oos/types/1">
  <notificationOK>
    <oos:id>8373125</oos:id>
    <oos:notificationNumber>0173200001513000422</oos:notificationNumber>

Here is my request

declare @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x,

'
<export xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns:q="http://bbhgtm.gov.com/oos/export/1" 
xmlns:oos="http://bbhgtm.gov.com/oos/types/1"/>

'


select *
from openxml(@hdoc, '/notificationOK/oos:id/oos:notificationNumber/', 1)
WITH(
      versionNumber int 'oos:versionNumber'
      ,createDate datetime 'oos:createDate'
      )

EXEC sp_xml_removedocument @hdoc

But I get NULL in my SQL table.

What to do?

Answer 1

You are ignoring XML namespaces in your XML document!

<export xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns="http://bbhgtm.gov.com/oos/export/1" 
        xmlns:oos="http://bbhgtm.gov.com/oos/types/1">

See those attributes xmlns=.....and xmlns:oos=......? They define the XML namespaces to consider when querying!

Also, I would recommend using native, native XQuery support , rather than clumsy OPENXMLcode.

Try this code here:

DECLARE @input XML = 
    '<export xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
             xmlns="http://bbhgtm.gov.com/oos/export/1" 
             xmlns:oos="http://bbhgtm.gov.com/oos/types/1">
         <notificationOK>
             <oos:id>8373125</oos:id>
             <oos:notificationNumber>0173200001513000422</oos:notificationNumber>
         </notificationOK>
     </export>'

;WITH XMLNAMESPACES('http://bbhgtm.gov.com/oos/types/1' AS oos, 
                    DEFAULT 'http://bbhgtm.gov.com/oos/export/1')
SELECT
    id = XC.value('(oos:id)[1]', 'int'),
    NotificationNumber = XC.value('(oos:notificationNumber)[1]', 'bigint')
FROM
    @input.nodes('/export/notificationOK') AS XT(XC)

: