1

I have 2 xml documents that I need to reference in a nested FLWOR statement.

MJH source.xml

<data-set>
 <record>
 <table>CONTACT</table>
 <column>AGGREGATE_REPORT</column>
 <change>Dropped</change>
 <logic>Related to agCenter</logic>
 </record>
<record>
 <table>QNR_DESIGN_TEMPLATE</table>
 <column>LOGO1</column>
 <change>Dropped</change>
 <logic>Related to agCenter</logic>
</record>
</data-set>

This is the outer document and I need to extract the 'table' and 'column' values per row.

MVReport - a collection of report documents that contain SQL statements.

....
<xml-property name="queryText"><![CDATA[select v.*
from poi_visit_details_v v
where v.site_id=? 
and v.std_code_id in ('15', 'TelephoneMonitoringVisit')
and to_char(v.actual_visit_date, 'yyyy-mm-dd') = substr(?, 0, 10)
and (reports_access_pk.site_access(v.SITE_ID, ?) = 'Y'
or reports_access_pk.superuser_access(?) = 'Y')
]]></xml-property>
<xml-property name="queryText"><![CDATA[select v.*
from poi_visit_details_v v
where v.site_id=? 
and v.std_code_id in ('15', 'TelephoneMonitoringVisit')
and to_char(v.actual_visit_date, 'yyyy-mm-dd') = substr(?, 0, 10)
and (reports_access_pk.site_access(v.SITE_ID, ?) = 'Y'
or reports_access_pk.superuser_access(?) = 'Y')
]]></xml-property>

....

This collection is the inner loop where I need to find all SQL statements that contain the word "select" and the 'table', 'column' values returned from the outer loop.

Here is my xquery.

The issue I have is how to reference the outer 'table','column' values in the inner loop's "where ... and ..." clauses?

for $target in doc("H:\MJH source.xml")/data-set/record
let $tTable := $target/table
let $tColumn := $target/column
for $sql in collection("MVReport") //*:xml-property[@name = "queryText"]
where $sql contains text "select"
and $sql contains $tTable
and $sql contains $tColumn
let $report := base-uri()
return <report><name>{$sql/base-uri()}</name><sql>{$sql}</sql></report>

Thanks for your help.

MJH
  • 21
  • 2
  • On the face of it, it looks as if you are referencing them quite successfully and correctly under the names `$tTable` and `$tColumn`. Is that not working for you? If not, what error messages are you getting? (Also, note that strictly speaking there is no outer `for` loop here, nor an inner: you have a single FLWOR expression, with multiple `for` and `let` clauses and a single `return` clause.) Are you confident of the syntax you're using for the Full-Text `contains` `text` operator? It looks like you may have omitted the keyword `text` by accident ... – C. M. Sperberg-McQueen Dec 04 '16 at 20:13
  • I'm interested in your statement that the query doesn't have an inner/outer loop. Will the query work as I expect? That is to loop through all the outer table/column combinations and search the inner loop collection for occurrences of "select" and the current table/column combination? – MJH Dec 04 '16 at 21:50
  • The query will work as I imagine you expect: the two `let` clauses will be assigned values for each distinct value of `$target`, and the `return` clause will be evaluated once for each distinct tuple of values (`$target`, `$tTable`, `$tColumn`, `$sql`). – C. M. Sperberg-McQueen Dec 05 '16 at 18:34

0 Answers0