[RESOLVED] XML Query returning NULL
I have the following data in an xml field in the database in a field called efulfillment_response:
Code:
<OperationComplete xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://insurance.travelinsurance.ca/services/efulfillment/">
<PolicyID>4817529</PolicyID>
<PolicyNumber>DT779209</PolicyNumber>
<RequestID>631505</RequestID>
<ApplicationRequestID />
<GenerationStatus>
<StatusFlag>0</StatusFlag>
<StatusString>Document Generation Complete</StatusString>
</GenerationStatus>
</OperationComplete>
I am trying to pull out the value in the StatusFlag field with both of the two following queries:
Code:
;WITH XMLNAMESPACES (
'http://insurance.travelinsurance.ca/services/efulfillment/' AS f
)
select efulfillment_response.value('/f:OperationComplete[1]/f:GenerationStatus[1]/StatusFlag[1]', 'varchar(10)') as status
from efulfillment_requests
where efulfillment_request_policy_id = '4817529'
;WITH XMLNAMESPACES (
'http://insurance.travelinsurance.ca/services/efulfillment/' AS f
)
select GenerationStatus.value('StatusFlag[1]', 'int')
from efulfillment_requests
cross apply efulfillment_response.nodes('/f:OperationComplete/f:GenerationStatus') as T1(GenerationStatus)
where efulfillment_request_policy_id = '4817529'
Both of them look correct to me, but both are returning NULL instead of 0. Can someone see what it is that I'm doing wrong?
Re: XML Query returning NULL
Never mind. Immediately after I posted, I realized that I was missing the f: on the StatusFlag field for both of them.