We're testing this voice authentication system at the telephone center. The testers want to save "everything" that goes between the IVR server and the voice authentication server, so I'm stuffing XML SOAP responses into a SQL Server 2005 database table. Today, one of the eggheads wants to see just the score and confidence values from the SOAP response for each test phone call.

Now, the root element in the SOAP response had a namespace defined. At first, I wasn't prefixing the element names with the namespace. I suspected this was a problem, but always got an error message about "redefining namespace" if I tried to declare the namespace inside the value() method. It turns out, if the source XML in your table has a namespace in it you must:

  • Declare a namespace inside the value() method call

  • The namespace must have a diferent name than what is already in the xml



When I joined together those two little details, I achieved XQuery nirvana.

select
CallId, UserId,
case authtype when 1 then 'enrollment' when 2 then 'verification' end as AuthType,
ResponseTime, SequenceNumber, ResultCode,
SOAPResponse.value('declare namespace tmp="http://craplite.shitaphonics.com"; (/tmp:FilePointerVerifyWithoutNewSettingsResult/tmp:Score)[1]', 'decimal(22,20)') AS Score,
SOAPResponse.value('declare namespace tmp="http://craplite.shitaphonics.com"; (/tmp:FilePointerVerifyWithoutNewSettingsResult/tmp:Confidence)[1]', 'decimal(22,20)') AS Confidence
FROM AuthResponses_Test
WHERE SOAPResponse.exist('declare namespace tmp="http://craplite.shitaphonics.com"; /tmp:FilePointerVerifyWithoutNewSettingsResult/tmp:Score')=1

order by callid, DiaphonicsUserId, ResponseTime, SequenceNumber