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?

Damascus

Right, the problem is that <AffordabilityResults> is not in a namespace, so when you query elements in the urn:callcredit.co.uk... namespace by default, you are unable to select it. Unfortunately there's no way to assign a prefix to no-namespace, so I think you have to use prefixes on the namespaced elements. – Gasholder

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.

Vasti

This turned out to be the simplest solution to my problem - thanks – Damascus

thanks for this, this helped me solve an issue where I did not care about the namespace – Accouter

This is Awesome! I work in a database (controlled by another company) where I cannot change the data or schema and we have some XML fields that are populated by a 3rd-Party for prescriptions. It's a mess with varying XML-Namepaces. This allows my queries to extract important information regardless of the Namespace they felt like using that day. – Albigenses

Thank you for this post. I found interesting behavior in MSSQL, where if a query is executed against a default namespace whose url cannot be reached, T-SQL returns nothing, and does not throw an error. Using this wildcard methodology solved the problem. – Melone