How to ignore XML namespace when creating SQL request?

Question

I have many rows in a DB which contain XML data field. XML approximately looks like this:

    <CabasEstimateReply xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://cabmb.cab.se/schemas/CABMBGeneralSchemas/CABASEstimateReply/2006-11-16/">
  <Estimate xmlns="">
    <WorkshopCompanyId>C002006893</WorkshopCompanyId>
    <EstimateId>1-SE-AEB965-634921885183891313</EstimateId>
  </Estimate>
  <EstimateReply xmlns="">
    **<EstimateReplyCode>ReplyStatus1</EstimateReplyCode>**
    <EstimateReplyVersion>1</EstimateReplyVersion>
    <EstimateReplyDate>2013-05-31T11:40:18.6227322+03:00</EstimateReplyDate>
    <EstimateReplyComment />
    <EstimateReplyMessage>Kunden betalar :                   8692    Fakturaadress :                    Trygg Hansa</EstimateReplyMessage>
    <EstimateReplyMessageCompressMethod />
    <EstimateReplyReference>010704</EstimateReplyReference>
    <EstimateReplyForthcomingInspectionDate />
  </EstimateReply>
  <Vehicle xmlns="">
    <VehicleRegNo>XND108</VehicleRegNo>
    <VehicleMake>BMW</VehicleMake>
    <VehicleModel>525I TOURING</VehicleModel>
    <VehicleModelYear />
    <VehicleModelMonth />
    <VehicleVINCode />
    <VehicleChassiNo>NL51010CM95684</VehicleChassiNo>
    <VehicleFirstRegistered>2006-02-23T00:00:00</VehicleFirstRegistered>
    <Imported>null</Imported>
  </Vehicle>

I need to have a possibility to get a value EstimateReplyCode(marked with bold) via SQL request. I'm doing this like:

 ;WITH XMLNAMESPACES(DEFAULT 'https://cabmb.cab.se/schemas/CABMBGeneralSchemas/CABASEstimateReply/2006-11-16/')
select [Data],
Data.value('(/CabasEstimateReply/EstimateReply/EstimateReplyCode)[1]', 'nvarchar(64)') AS ReplyCode
from EstimateReplyRawData

But get only null values for ReplyCode. When I tried to convert XML to string, then replace namespaces and then convert to XML back everything worked well, that's why I suppose that the issue is the namespace. What am I doing wrong here?

Anon · Accepted Answer · 2013-12-13 15:28:41Z

2

If you really want to ignore namespaces, you can use namespace wildcards.

select [Data],
Data.value('(/*:CabasEstimateReply/*:EstimateReply/*:EstimateReplyCode)[1]', 'nvarchar(64)') AS ReplyCode
from EstimateReplyRawData

answered Dec 13, 2013 at 15:28

Anon

10.9k1 gold badge32 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to ignore XML namespace when creating SQL request?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related