SQL Server XQuery with Default Namespace

Question

I've got some XML Data in a SQL Server Table in an XML Column as follows:

<AffordabilityResults>
      <matchlevel xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">IndividualMatch</matchlevel>
      <searchdate xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">2013-07-29T11:20:53</searchdate>
      <searchid xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">{E40603B5-B59C-4A6A-92AB-98DE83DB46E7}</searchid>
      <calculatedgrossannual xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">13503</calculatedgrossannual>
  <debtstress xmlns="urn:callcredit.co.uk/soap:affordabilityapi2">
    <incomedebtratio>
      <totpaynetincome>0.02</totpaynetincome>
      <totamtunsecured>0.53</totamtunsecured>
      <totamtincsec>0.53</totamtincsec>
    </incomedebtratio>
  </debtstress>
</AffordabilityResults>

You'll note that some of the elements have an xmlns attribute and some don't...

I need to write queries to return the data - and more importantly show a business analyst how to write her own queries to get the data she needs so I want it to be as simple as possible.

I can query the data easily using the WITH XMLNAMESPACES element as follows:

WITH XMLNAMESPACES (N'urn:callcredit.co.uk/soap:affordabilityapi2' as x )
SELECT

    ResponseXDoc.value('(/AffordabilityResults/x:matchlevel)[1]','varchar(max)' ) AS MatchLevel
  , ResponseXDoc.value('(/AffordabilityResults/x:debtstress/x:incomedebtratio/x:totamtunsecured)[1]','nvarchar(max)' ) AS UnsecuredDebt

  FROM [NewBusiness].[dbo].[t_TacResults]

But adding the x: part to the query makes it look overly complicated, and I want to keep it simple for the business analyst.

I tried adding:

WITH XMLNAMESPACES (DEFAULT 'urn:callcredit.co.uk/soap:affordabilityapi2' )

and removing the x: from the XQuery - but this returns null (possibly because of the lack of the xmlns on the root element?)

Is there any way I can simplify these queries either with or without the default namespace?

Answer 1

If namespaces are not important in your use case, you could use the namespace wildcard selector *:, which both selects nodes without and with arbitrary namespaces.

An example query could be

(/*:AffordabilityResults/*:matchlevel)[1]

The business analyst will still have to add the selector in front of every node test, but it's the same "prefix" all the time and the only error to be expected is forgetting to use it somewhere.