Post reply

XQuery question

  • January 11, 2016 at 8:59 am

    #399550

    I had a crash course on XQuery over this weekend as I have a client that is in need of some text data out of an XML column in a database table. Using a simple query, I was able to extract those dates, however each client that we have in this database table has many nodes (called <benefit>), several benefit types (<type>). So now I need to figure out how to get just those <benefit> nodes where <info> is just "Spend Down" (this is a text() value). I have the following xquery, but it is returning still ALL the dates from every <benefit> node. Any help is appreciated

    select clientid

    , VerifiedXMLResponse.query('/eligibilityresponse/subscriber/benefit/date-of-service/text()') as [dates]

    FROM ElectronicEligibilityVerificationRequests

    WHERE (ElectronicEligibilityVerificationBatchId IN (1052) )

    AND (VerifiedResponseText LIKE '%SPENDOWN%')

    and VerifiedXMLResponse.exist('/eligibilityresponse/subscriber/benefit/info/text()[contains(., "Spend Down")]')=1

    order by ClientId

  • January 11, 2016 at 12:23 pm

    #1851095

    Brad Allison (1/11/2016)

    I had a crash course on XQuery over this weekend as I have a client that is in need of some text data out of an XML column in a database table. Using a simple query, I was able to extract those dates, however each client that we have in this database table has many nodes (called <benefit>), several benefit types (<type>). So now I need to figure out how to get just those <benefit> nodes where <info> is just "Spend Down" (this is a text() value). I have the following xquery, but it is returning still ALL the dates from every <benefit> node. Any help is appreciated

    select clientid

    , VerifiedXMLResponse.query('/eligibilityresponse/subscriber/benefit/date-of-service/text()') as [dates]

    FROM ElectronicEligibilityVerificationRequests

    WHERE (ElectronicEligibilityVerificationBatchId IN (1052) )

    AND (VerifiedResponseText LIKE '%SPENDOWN%')

    and VerifiedXMLResponse.exist('/eligibilityresponse/subscriber/benefit/info/text()[contains(., "Spend Down")]')=1

    order by ClientId

    This seams pretty easy. Do you have a sample of the XML data?

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply