XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
ling shenSubject: 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

Postnext
Minollo I.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')

Postnext
ling shenSubject: 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

Postnext
Minollo I.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.

Postnext
ling shenSubject: 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>




Postnext
Minollo I.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...

Postnext
ling shenSubject: 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 "&lt;" and ">" is presented as "&gt;
" in the Response.xml, I got empty sequence again and nothing was updated in the table.

&lt;RecordResponse&gt;
&lt;Message&gt;
&lt;MessageCode&gt;-1&lt;/MessageCode&gt;
&lt;MessageText&gt;Column 1 is invalid&lt;/MessageText&gt;
&lt;/Message&gt;
&lt;/RecordResponse&gt;


thanks,


Ling

Postnext
Minollo I.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.

Postnext
ling shenSubject: 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.



Unknownhelpticket.zip
It contains 4 files

Posttop
Minollo I.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
...

 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!  
go

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.