Sp_xml_preparedocument and the use of the namespace. I have an error & ldquo; XML parsing error: reference to the namespace prefix not declared: 'a'. & Rdquo; In SQL Server 2012

advertisements

Please help. After reading all google and stackoverflow :) my brain does not work anymore.

I have below TSQL (running on SQL Server 2012)

I cannot figure out where I need to declare my a: namespace? And how many namespaces I need to declare?

DECLARE @XML AS XML
DECLARE @hDoc AS INT

SELECT @XML = '<GetAssetWarrantyResponse xmlns="http://tempuri.org/">
  <GetAssetWarrantyResult xmlns:a="http://schemas.datacontract.org/2004/07/Dell.AWR.Domain.Asset" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <a:Faults />
    <a:Response>
      <a:DellAsset>
        <a:AssetParts i:nil="true" />
        <a:CountryLookupCode>5252</a:CountryLookupCode>
        <a:CustomerNumber>645651</a:CustomerNumber>
      </a:DellAsset>
    </a:Response>
  </GetAssetWarrantyResult>
</GetAssetWarrantyResponse>'

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT CountryLookupCode
FROM OPENXML(@hDoc, 'GetAssetWarrantyResponse/GetAssetWarrantyResult/a:Response/a:DellAsset')
WITH
(
CountryLookupCode   [nvarchar](20)  'a:CountryLookupCode'
)

EXEC sp_xml_removedocument @hDoc
GO


You need to specify the namespace prefixes when invoking sp_xml_preparedocument. In this case you have the a namespace and the default namespace (the one without prefix: xmlns="....") :

EXEC sp_xml_preparedocument
        @hDoc OUTPUT
        , @XML
        , '<root xmlns:d="http://tempuri.org/" xmlns:a="http://schemas.datacontract.org/2004/07/Dell.AWR.Domain.Asset"/>'

And use the registered prefixes properly :

SELECT CountryLookupCode
FROM OPENXML(@hDoc, 'd:GetAssetWarrantyResponse/d:GetAssetWarrantyResult/a:Response/a:DellAsset')
WITH
(
    CountryLookupCode   [nvarchar](20)  'a:CountryLookupCode'
)