SQL Server XQuery with a default namespace

Question

I have XML data in a SQL Server table in an XML column:

<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 will notice that some elements have the xmlns attribute, and some do not ...

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

I can easily query the data 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 x: part to the query makes it overly complex, and I want it to be easy for the business analyst.

I tried adding:

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

and removing x: from XQuery - but this returns null (perhaps due to the lack of xmlns in the root element?)

Can these queries be simplified with or without a default namespace?

Answer 1

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

An example request could be

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

Business analysts will still have to add a selector before each node test, but it is still the same “prefix” all the time and the only error that should be expected is forgetting to use it somewhere.