Artem Vaskanyan Artem Vaskanyan - 5 years ago 148
SQL Question

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

I'm trying to parse an XML document with a query.

Here is a sample 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 query

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'm getting NULL in my SQL table.

What to do?

Answer Source

You're ignoring the XML namespaces on 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 xmlns=..... and xmlns:oos=...... attributes? Those define XML namespaces that need to be taken into account when querying!

Also, I'd recommend to use the built-in, native XQuery support rather than the clumsy OPENXML code.

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)

This results in an output something like this:

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download