Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:ling shen Date:18 Jul 2008 01:55 PM
Hello,
I need help on how to loop through a set of extract values from the following xml file and update corresponding records in a table.
let $table := collection("myTable")/mytable[STATUS !='Y']
let $group := $table[fn:position() le $pageSize]
for $x in $response/ns1:uploadResponseElement/ns1:result/ns2:TransactionResponse/ns2:UploadResponse/ns2:RecordResponse/ns2:Message/ns2:MessageCode
let $MessageCode:= ddtek:parse($x/ns2:Message/ns2:MessageCode)
return data($x)
I need to update column "status" in an oracle table "mytable" based on different MessageCode for each record. How do I get the MessageCode one at a time instead of all of them at them same time as a string?
Question 2.
For each message code, I need to perform the following update.
if ( $MessageCode = '0' ) then
ddtek:sql-update($group,"STATUS",'Y',"PROCESS_IND",'1')
else
ddtek:sql-update($group,"STATUS",'F',"PROCESS_IND",'1')
Since the order of the MessageCode in the response.xml file is the same as the order of records in myTable, how do I add a where clause to the ddtek:sql-update to update the correct record?
(where process_ind=0 and rownum=1)
Is there a way in xquery to match the MessageCode in response.xml file to the records in myTable without the where clasue?
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:Minollo I. Date:18 Jul 2008 02:38 PM Originally Posted: 18 Jul 2008 02:34 PM
>Question 1.
>I need to update column "status" in an oracle table "mytable" based
>on different MessageCode for each record. How do I get the
>MessageCode one at a time instead of all of them at them same time as
>a string?
...you are already creating the result sequence one by one; see suggested XQuery below.
>Question 2.
>For each message code, I need to perform the following update.
>...
>Since the order of the MessageCode in the response.xml file is the
>same as the order of records in myTable, how do I add a where clause
>to the ddtek:sql-update to update the correct record?
>(where process_ind=0 and rownum=1)
>Is there a way in xquery to match the MessageCode in response.xml
>file to the records in myTable without the where clasue?
I'm not positive I undertand this; are you saying that the first <MessageCode> element in response.xml is about the first row in your database which matches process_ind=0 and rownum=1? And that the same applies to the order of the following <MessageCode> elements and filtered rows? How is the order of rows in the table defined? Sounds a bit dangerous to not force a specific order based on some column, but if that's what you need, something like may solve your problem (notice the "unordered" option at the top):
declare ordering unordered;
declare namespace ns1 = "http://myXML/";
declare namespace ns2 = "http://www.mycompany.org/namespaces/T0001V2";
declare variable $response as document-node(element(*, xs:untyped)) external;
for $MessageCode at $pos in $response/ns1:uploadResponseElement/ns1:result/ns2:TransactionResponse/ns2:UploadResponse/ns2:RecordResponse/ns2:Message/ns2:MessageCode
let $row := (collection("myTable")/myTable[PROCESS_IND=0 and ROWNUM=1])[$pos]
return
if ( $MessageCode = '0' ) then
ddtek:sql-update($row,"STATUS",'Y',"PROCESS_IND",'1')
else
ddtek:sql-update($row,"STATUS",'F',"PROCESS_IND",'1')
If instead you talking about a specific table order (based on a column value), then you probably want something like this:
declare namespace ns1 = "http://myXML/";
declare namespace ns2 = "http://www.mycompany.org/namespaces/T0001V2";
declare variable $response as document-node(element(*, xs:untyped)) external;
for $MessageCode at $pos in $response/ns1:uploadResponseElement/ns1:result/ns2:TransactionResponse/ns2:UploadResponse/ns2:RecordResponse/ns2:Message/ns2:MessageCode
let $row := (for $item in collection("myTable")/myTable[PROCESS_IND=0 and ROWNUM=1] order by $item/SOME_FIELD return $item)[$pos]
return
if ( $MessageCode = '0' ) then
ddtek:sql-update($row,"STATUS",'Y',"PROCESS_IND",'1')
else
ddtek:sql-update($row,"STATUS",'F',"PROCESS_IND",'1')
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:ling shen Date:18 Jul 2008 04:16 PM
Tested the code and no update happens.
Can I see the value of $MessageCode within the for loop? I used return $MessageCode and nothing showed up in the preview panel
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:Minollo I. Date:18 Jul 2008 04:33 PM
I slightly modified the xquery after posting it as I was using a part of your code that didn't fit well with the other changes; make sure you are using that modified version (the one containing "for $MessageCode"...).
Anyway, you can easily debug what's going on changing the update block into:
if ( $MessageCode = '0' ) then
<update><row>{$row}</row>
ddtek:sql-update($row,"STATUS",'Y',"PROCESS_IND",'1')
</update>
else
<update><row>{$row}</row>
ddtek:sql-update($row,"STATUS",'F',"PROCESS_IND",'1')
</update>
...that won't update anything for sure, but it will return back data about what it should do.
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:ling shen Date:18 Jul 2008 05:03 PM
This is what I have now. Nothing returned.
declare ordering unordered;
declare namespace ns1 = "http://myXML/";
declare namespace ns2 = "http://www.mycompany.org/namespaces/T0001V2";
declare variable $response as document-node(element(*, xs:untyped)) external;
for $MessageCode at $pos in $response/ns1:uploadResponseElement/ns1:result/ns2:TransactionResponse/ns2:UploadResponse/ns2:RecordResponse/ns2:Message/ns2:MessageCode
let $row := (collection("myTable")/myTable[PROCESS_IND=0])[$pos]
return
if ( $MessageCode = '0' ) then
<update><row>{$row}</row>
ddtek:sql-update($row,"STATUS",'Y',"PROCESS_IND",'1')
</update>
else
<update><row>{$row}</row>
ddtek:sql-update($row,"STATUS",'F',"PROCESS_IND",'1')
</update>
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:Minollo I. Date:18 Jul 2008 05:14 PM
Running that using the response.xml file you attached, I do get output.. if you don't get anything, it means the for is iterating on an empty sequence...
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:ling shen Date:24 Jul 2008 06:31 PM
The code is working with the XML given at the beginning.
However when "<" is presented as "<" and ">" is presented as ">
" in the Response.xml, I got empty sequence again and nothing was updated in the table.
<RecordResponse>
<Message>
<MessageCode>-1</MessageCode>
<MessageText>Column 1 is invalid</MessageText>
</Message>
</RecordResponse>
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:Minollo I. Date:24 Jul 2008 09:07 PM
I bet it doesn't; if "<" and ">" are escaped, that becomes a block of text, no more of elements. The XPath navigation will sadly fail.
Can you share an XML example consistent with what you actually need to deal with? That will make it easier to provide a solution.
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:ling shen Date:25 Jul 2008 01:25 AM
I will try to attach the acutal code and xml files mentioned below.
1. Real XML file that need to be processed:
UCRUploadToVolpe200807031831_1_Resp_orig.xml
This is a well-formated XML using "check well-formated" function in Stylus;
It appears to have an empty sequence with MessageCode using loop_through_with messagecode.xquery;
However it does work with no_loop_with_resultcode.xquery without looping through the messgecode.
2. XML file that has been modified to use "<" and "</":
UCRUploadToVolpe200807031831_1_Resp.xml
Both loop_through_with messagecode.xquery and no_loop_with_resultcode.xquery works with this XML file.
Subject:How to loop through a set of extraxt values from a xml file and update corresponding records in a table Author:Minollo I. Date:25 Jul 2008 08:46 AM
To work with the escaped document, you'll need to change the loop into:
...
for $MessageCode at $pos in ddtek:parse($response/ns1:uploadUCRResponseElement/ns1:result)/ns2:TransactionResponse/ns2:UploadResponse/ns2:RecordResponse/ns2:Message/ns2:MessageCode
...