Results 1 to 5 of 5

Thread: XML Search

  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Unanswered: XML Search

    Hey All

    I need to search a XML tag which goes several levels down. And this needs to be done in the where condition so if that condition satisfy
    results can be return

    like function cannot use here since i need to find the path to the correct tag

    Here is the tag order

    ShipmentManifestBusinessRule --> packingManifestConfig --> documentSection type= “Screens” && documentSectionFields fieldPath =” ShipmentManifest_PackingManifest_InnermostPack” --> fieldAction=” Write-Limited”

    I'm attaching the XML as well. attachment XML.txt
    Your help would be appreciated

    SELECT
    ruleId,
    ruleType,
    xml,
    status,
    originalId,
    ownerOrgId,
    ownerOrgType

    FROM
    BusinessRule
    WHERE
    originalId = 1000067595
    AND xmlcast(xmlquery('$x/ShipmentManifestBusinessRule/packingManifestConfig/documentSection/documentSectionFields/fieldAction' passing xmlparse(document BusinessRule.xml)as "x") as varchar(30)) = 'Write-Limited'
    -- XML like '%Write-Limited%'
    AND status = 'Active'

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Not sure what your question is, but consider using the XMLEXISTS() function.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2011
    Posts
    85
    Hey n_i

    If you look at the attachment of the XML [See my first post XML.txt]
    and go for the tags like below

    ShipmentManifestBusinessRule --> packingManifestConfig --> documentSection type= “Screens” && documentSectionFields fieldPath =” ShipmentManifest_PackingManifest_InnermostPack” --> fieldAction=” Write-Limited”

    You will get the problem i have and i think the query i wrote is kind a match but cant figure the remaining part that i'm missing.

    Also i need this exact path is because there are other tags as well where it contains some similar data
    But i want data from <packingManifestConfig> tag
    So thats why i think this is the best option

    Thanx

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ai_zaviour View Post
    If you look at the attachment of the XML ... You will get the problem i have
    That's the interesting way to pose a question. Let me try to respond in kind: If you look at the DB2 manual you will find a way to solve the problem.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select x.*
    from 
      BusinessRule t
    --, xmltable ('$d/ShipmentManifestBusinessRule/packingManifestConfig/documentSection/documentSectionFields[fieldAction/text()="Write-Limited"]' passing xmlparse(document t.x) as "d"
    , xmltable ('$d/ShipmentManifestBusinessRule/packingManifestConfig/documentSection/documentSectionFields' passing xmlparse(document t.x) as "d"
    columns
      fieldName       varchar(50)      PATH 'fieldName'
    , fieldAction     varchar(50)      PATH 'fieldAction'
    ) x
    WHERE
    originalId = 1000067595 
    and xmlexists ('$d/ShipmentManifestBusinessRule/packingManifestConfig/documentSection/documentSectionFields[fieldAction/text()="Write-Limited"]' passing xmlparse(document t.x) as "d")
    AND status = 'Active'
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •