OpenXML: XML parsing error: Reference to undeclared namespace prefix: 'n1'

[Jeremy Chapman]

I'm getting a strange xml parsing error "Reference to undeclared namespace
prefix: 'n1'" that I can't explain.

I've included the xml and my sql below. It's strange because the first
select succeeds, but the second select (for the transaction node) failes
with the above error. Is my OpenXML statement incorrect?

DECLARE @xmlMessage varchar(8000)
SELECT xmlMessage =
'<MESSAGE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns=http://org.test>
<PAYLOAD>
<PROVIDER>
<CHANGED_RECORD>
<PROVINCE_CODE>BC</PROVINCE_CODE>
<TRANSACTION>
<IT_CODE>a</IT_CODE>
</GRS_TRANSACTION>
</CHANGED_RECORD>
</PROVIDER>
</PAYLOAD>
</MESSAGE>'



DECLARE @idoc int
EXECUTE sp_xml_preparedocument @idoc OUTPUT, @xmlMessage, '<root
xmlns:ns1=http://org.test/>'

SELECT
*
FROM
OpenXML(@idoc,
'/ns1:MESSAGE/ns1:PAYLOAD/ns1:PROVIDER/ns1:CHANGED_RECORD')
WITH ([PROVINCE_CODE] varchar(15) './ns1:PROVINCE_CODE') --string,
length 0..15, occures 1..1

SELECT
*
FROM
OpenXML(@idoc,
'/ns1:MESSAGE/ns1:PAYLOAD/ns1:PROVIDER/ns1:CHANGED_RECORD/n1:TRANSACTION')
WITH ([IT_CODE] varchar(90) './ns1:IT_CODE') --string, length 0..90,
occures 1..1

EXECUTE sp_xml_removedocument @iDoc

[Alejandro Mesa]

Post by Jeremy Chapman
'<MESSAGE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns=http://org.test>

'<MESSAGE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://org.test">

Post by Jeremy Chapman
<TRANSACTION>
<IT_CODE>a</IT_CODE>
</GRS_TRANSACTION>

<TRANSACTION>
<IT_CODE>a</IT_CODE>
</TRANSACTION>

Post by Jeremy Chapman
xmlns:ns1=http://org.test/>'

EXECUTE sp_xml_preparedocument @idoc OUTPUT, @xmlMessage, '<MESSAGE
xmlns:ns1="http://org.test"/>'


AMB

Post by Jeremy Chapman
I'm getting a strange xml parsing error "Reference to undeclared namespace
prefix: 'n1'" that I can't explain.
I've included the xml and my sql below. It's strange because the first
select succeeds, but the second select (for the transaction node) failes
with the above error. Is my OpenXML statement incorrect?
SELECT xmlMessage =
'<MESSAGE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns=http://org.test>
<PAYLOAD>
<PROVIDER>
<CHANGED_RECORD>
<PROVINCE_CODE>BC</PROVINCE_CODE>
<TRANSACTION>
<IT_CODE>a</IT_CODE>
</GRS_TRANSACTION>
</CHANGED_RECORD>
</PROVIDER>
</PAYLOAD>
</MESSAGE>'
xmlns:ns1=http://org.test/>'
SELECT
*
FROM
'/ns1:MESSAGE/ns1:PAYLOAD/ns1:PROVIDER/ns1:CHANGED_RECORD')
WITH ([PROVINCE_CODE] varchar(15) './ns1:PROVINCE_CODE') --string,
length 0..15, occures 1..1
SELECT
*
FROM
'/ns1:MESSAGE/ns1:PAYLOAD/ns1:PROVIDER/ns1:CHANGED_RECORD/n1:TRANSACTION')
WITH ([IT_CODE] varchar(90) './ns1:IT_CODE') --string, length 0..90,
occures 1..1

[Stefan Delmarco]

This line is the problem:

EXECUTE sp_xml_preparedocument @idoc OUTPUT, @xmlMessage, '<root xmlns:ns1=http://org.test/>'

You need to delimit the namespace literal. Change it to:

EXECUTE sp_xml_preparedocument @idoc OUTPUT, @xmlMessage, '<root "xmlns:ns1=http://org.test"/>'


Also, change this line:

'/ns1:MESSAGE/ns1:PAYLOAD/ns1:PROVIDER/ns1:CHANGED_RECORD/n1:TRANSACTION')

The namespace prefix should be ns1 and not n1:

'/ns1:MESSAGE/ns1:PAYLOAD/ns1:PROVIDER/ns1:CHANGED_RECORD/ns1:TRANSACTION')