XML Query Request

Hi,

I am after the xml query to get the results where account paidInd=false. I have masked the namespace definition for security purpose.

<tns:inPatientMedicalClaimRequest xmlns:tns="http://xxx.xxx.xxx" rhboIsoNum="90360009">
  <claim claimTypeCde="xx" facilityId="12545" fundBrandId="ABC" accountReferenceId="0000" accountPaidInd="false"

I have used the below query but it did not work. Any help is highly appreciated.


SELECT TOP 1 cast(xml_request AS XML) AS xml_response
INTO #temp
FROM xx

DECLARE @t TABLE (x XML)

INSERT @t
SELECT xml_response
FROM #temp

SELECT x.value('(/*:inPatientMedicalClaimRequest/claim/', 'varchar(100)') AS accountPaidInd
FROM @t
WHERE x.exist('(/*:inPatientMedicalClaimRequest/claim/accountPaidInd[contains(.,"false")])') = 1

Regards,

kccrga

June 19th, 2015 12:59am

It throws the error

DECLARE @xml AS XML ='<tns:inPatientMedicalClaimRequest xmlns:tns="http://xxx.xxx.xxx" rhboIsoNum="90360009">
  <claim claimTypeCde="xx" facilityId="12545" fundBrandId="ABC" accountReferenceId="0000" accountPaidInd="false"'



DECLARE @t TABLE (x XML)

INSERT INTO @t SELECT @xml

Msg 9400, Level 16, State 1, Line 2
XML parsing: line 2, character 112, unexpected end of input

See the example below works for me (using WHERE condition)

IF OBJECT_ID('StudXML', 'U') IS NOT NULL DROP TABLE StudXML
CREATE TABLE StudXML(S_ID INT, StudData XML)
GO
 
INSERT INTO StudXML(S_ID, StudData)
SELECT 1, '<Student S_ID = "1">
            <Subject Sub_ID = "20A" Sub_Marks = "100" />
            <Subject Sub_ID = "20B" Sub_Marks = "100" />
           </Student>'
            
INSERT INTO StudXML(S_ID, StudData)
SELECT 2, '<Student S_ID = "2">
            <Subject Sub_ID = "20C" Sub_Marks = "100" />
            <Subject Sub_ID = "20D" Sub_Marks = "100" />
           </Student>'
            
            



SELECT S_ID FROM StudXML
WHERE StudData.exist('/Student/Subject[@Sub_ID = "20C"]')= 1

Free Windows Admin Tool Kit Click here and download it now
June 19th, 2015 1:12am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics