First get HotFix #3 from SAG support!!!
Even with this I can still crash the DB
if I do some odd-ball schema definition.
X-node configuration in 3.1 is very cryptic,
not documented well at all and there are no reasonable examples. I have spent more than
three days to unravel this mystery by reverse
engineering and basic detectective work. The
one thing that I find lacking that would be
of most use are some real exapmles. The field
in the schema editor called accessPredicate is
a free form text field and there are no explainations of what to put in it or the syntax.
I am somewhat surprised that a SQL join can’t
be done since the ‘table’ field will only allow
one table. To overcome this you must create views
(Oracle) or queries (Access) to map the join to
a single table.
So here is how it breaks down: The basic SELECT statement gets formed this way…
SELECT rows FROM table WHERE condition
‘rows’ come from the primarykeyColumn field
and all the column names you want to access in the NodeSQL children.
‘table’ comes from the Table field and only one is allowed so you can’t do a join in the ‘condition’ field of the SELECT
‘condition’ is the tricky one as it is filled in from the accessPredicate field in the schema editor on the SubTreeSQL map type. The syntax is
not bad one you know the trick. I used this:
col=tsd:nodeParameter/keyElement/@key</tsd:nodeParameter>
‘col’ is the column in ‘table’ that I want to
match the attribute names ‘key’ on the top level
element ‘keyElement’. The tsd:nodeParameter takes
an X-Path whose value is used in the SQL select.
The data type of the ‘@key’ in the schema definition must match the data type of the ‘col’
column - xs:integer or xs:string for example.
If you want to use ‘<’ instead of ‘=’ just use
<
This all lets you do Hybrid mapping of data into
your XML documents where each XML instance has
some unique ID or part number that can be looked
up in a relational DB and extract specific data
as opposed to just getting all the rows in a table
spit out as XML. Remember to set ignoreUpdate to
true for ODBC extract only situations.
Another side effect I noticed was that if the XML
file did not have the node mapped as NodeSQL you will get one less item than are returned in the SELECT. So if the condition is ‘=’ you get nothing
if it is < or > one gets dropped and an empty element is inserted in its place - Ouch!.
I’m sure that I could write a book on this topic
but this is not the forum for that. Just reply to
this topic if you have more questions. I’ll even
post real examples if asked.
So lets hear your stories,
Dave