|
|
 next
|
  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.
*************************
Response.xml
<ns1:uploadResponseElement xmlns:ns1="http://myXML/">
<ns1:result><TransactionResponse xmlns="http://www.mycompany.org/namespaces/T0001V2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mycompany.org/namespaces/T0001V2 T0001V2.xsd">
<UploadResponse>
<ResultCode>1</ResultCode>
<ResultDescription>Partial</ResultDescription>
<ReceivedRecordCount>6</ReceivedRecordCount>
<AppliedRecordCount>2</AppliedRecordCount>
<RecordResponse>
<Message>
<MessageCode>-1</MessageCode>
<MessageText>Column 1 is invalid</MessageText>
</Message>
</RecordResponse>
<RecordResponse>
<Message>
<MessageCode>14</MessageCode>
<MessageText>Failed validation</MessageText>
</Message>
</RecordResponse>
<RecordResponse>
<Message>
<MessageCode>-1</MessageCode>
<MessageText>Column 1 is invalid</MessageText>
</Message>
</RecordResponse>
<RecordResponse>
<Message>
<MessageCode>0</MessageCode>
<MessageText>Successfully</MessageText>
</Message>
</RecordResponse>
<RecordResponse>
<Message>
<MessageCode>0</MessageCode>
<MessageText>Successfully</MessageText>
</Message>
</RecordResponse>
<RecordResponse>
<Message>
<MessageCode>-1</MessageCode>
<MessageText>Column 1 is invalid</MessageText>
</Message>
</RecordResponse>
</UploadResponse>
</TransactionResponse>
</ns1:result>
</ns1:uploadResponseElement>
*************************
xquery in Stylus:
declare namespace ns1 = "http://myXML/";
declare namespace ns2 = "http://www.mycompany.org/namespaces/T0001V2";
declare variable $response as document-node(element(*, xs:untyped)) external;
declare variable $pageSize := 100;
declare variable $cnt := 0;
declare variable $UPLOAD_DT:=xs:dateTime(fn:current-dateTime());
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)
RESULT is:
-1 14 -1 0 0 -1
**************************************
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?
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?
Thank you.
Ling
|
|
next
|
  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')
|
|
next
|
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.
|
|
|
|
|
helpticket.zip
top