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?