Member Avatar for crazykoolit

Good Day!
We would like to seek help on managing multiple values within a single XML attribute.

<Row>
        <title>type Title here</title>
        <description>type Description here</description>
        <stage>Development</stage>
        <platform>Java, .NET, Mainframe</platform>
        <lifecycle>Custom Development</lifecycle>
        <servicegroup>System Integration (SI), Technology Consulting (TC)</servicegroup>
</Row>

With the above sample xml, as you may notice, <platform> does have 3 values; (1) Java, (2) .NET and (3) Mainframe. We were able to load them separately.

NOTE: We have 4 filters; (1) stage, (2) platform, (3) lifecycle and (4) servicegroup.

PROBLEM: When we try to filter the platform and servicegroup.

Scenario#1, we selected "Java" in the platform filter and use "where" condition.

RESULT: It loads those items that has <platform>Java</platform> only and does not include <platform>Java, .NET, Mainframe</platform>. [BAD RESULT]

Scenario#2, we selected "Java" in the platform filter and use "like" condition.

RESULT: It loads all items that has <platform>Java</platform> and include <platform>Java, .NET, Mainframe</platform>. [GOOD RESULT]

But the catch in using "like" condition is that it conflicts with platform values for "Oracle"...

 <platform>Oracle</platform>
 <platform>Oracle - PeopleSoft</platform>
 <platform>Oracle - Retek</platform>
 <platform>Oracle - Siebel</platform>

Oracle - PeopleSoft, Oracle - Retek and Oracle - Siebel are different to each other. So, what if I selected "Oracle" for platform filter? We expected items which only have <platform>Oracle</platform> and not all of those items that has "Oracle".

Any help appreciated.

  • 2 Contributors
  • 1 Reply
  • 268 Views
  • 40 Minutes Discussion Span
  • Latest Post Latest Post by Mike Askew

Dani AI

Generated

Quick summary: has a single XML element containing comma-separated values, and suggested using contains. contains (and SQL LIKE) simply does substring matching, so it fixes the simple case but produces false positives for variants like "Oracle - PeopleSoft". Two practical paths follow: fix the XML shape, or do token-aware matching at query time.

If the XML can be changed, normalize it so each value is its own element (or an attribute). That makes queries trivial, indexable and unambiguous:

/Row/platform[text() = 'Oracle']

If transformation is needed at ingest, split the CSV into separate <platform> nodes while parsing.

If the XML cannot be changed, use token-aware checks. Simple, reliable options:

  • SQL regex on the extracted string (Oracle example):

    WHERE REGEXP_LIKE(
EXTRACTVALUE(xml_col, '/Row/platform'),
'(^|,)\s*Oracle\s*(,|$)',
'i'
)

    This matches whole tokens only (start/comma and end/comma anchors) and is case-insensitive.

  • XQuery/XPath 2.0 tokenize approach:

    let $plats := tokenize(normalize-space(/Row/platform), '\s*,\s*')
return some $p in $plats satisfies $p = 'Java'

    Tokenize + normalize-space trims whitespace and avoids substring pitfalls.

Troubleshooting tips: always trim tokens and do case-insensitive compares; be careful with punctuation (".NET") and hyphenated variants — treat them as separate tokens or keep a canonical platform field. For high-volume queries, normalize at ingestion or build a lookup table (row_id → platform_token) and index it; that gives the best performance and removes ambiguity.

Member Avatar for Mike Askew

Could you try, a 'contains' condition?

This will allow you to match specific strings within the node.

Not sure how your implementing the condition so cant suggest code until I know that. But contains should do the job :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.