Script which fill data in XML file

zb99 · July 4, 2012, 8:03am

Hello,

I need help for writing a script that fills already generated xml file with data from oracle database and random sequences.

For example if we have the following tags:

<ns1:message>
        <ns1:messageId>        </ns1:messageId>
        <ns1:languageCode>  </ns1:languageCode>
</ns1:message>

<messageId> should be filled with random sequence.
<ns1:languageCode> should be filled with a value from the database.

Thank you

---------- Post updated 04-07-12 at 03:03 PM ---------- Previous update was 03-07-12 at 11:53 PM ----------

I have found the following solution which is partly useful for me.

#!/bin/bash
awk 'BEGIN{ print "<message>"}
{print "<ns1:messageId>"$1"</ns1:messageID>"}
{print "<ns1:languagecode>"$1"</ns1:languagecode>"}
END{ print "</message>"}' ik.dat

So using that I have first to export the queries to a file and then export the data from it in the XML.
Is there a way to export sql queries directly to the xml?

Corona688 · July 4, 2012, 11:18am

You could try lots of SELECT CONCAT("string", col, "string", col, ...) to assemble the text you want.

zb99 · July 5, 2012, 5:00am

Thanks for the good idea.

I will use CONCAT to export the values from the database + tags. Like this:

SELECT '<ns1:messageId>' || ' ' || seq.nextval || '</ns1:messageId>'  FROM dual;

<ns1:messageId> 22</ns1:messageId>

Could you suggest me what to use in order to add the fixed values such as <<?xml version="1.0" encoding="UTF-8"?>> and how to export both(fixed values + result of queries) in a file?

---------- Post updated at 12:00 PM ---------- Previous update was at 10:42 AM ----------

I try to use this:

SQL> spool test2.txt
SQL> set serveroutput on
SQL> begin
  2  dbms_output.put_Line('<?xml version="1.0" encoding="UTF-8"?>');
  3  end;
  4  /
<?xml version="1.0" encoding="UTF-8"?>

PL/SQL procedure successfully completed.

SQL> SELECT '<ns1:messageId>' || ' ' || imk.nextval || '</ns1:messageId>'  FROM dual;

'<NS1:MESSAGEID>'||''||IMK.NEXTVAL||'</NS1:MESSAGEID>'
--------------------------------------------------------------------------------
<ns1:messageId> 47</ns1:messageId>

SQL> spool off

And here is the result:

cat test2.txt:

SQL> set serveroutput on
SQL> begin
  2  dbms_output.put_Line('<?xml version="1.0" encoding="UTF-8"?>');
  3  end;
  4  /
<?xml version="1.0" encoding="UTF-8"?>                                          

PL/SQL procedure successfully completed.

SQL> SELECT '<ns1:messageId>' || ' ' || imk.nextval || '</ns1:messageId>'  FROM dual;

'<NS1:MESSAGEID>'||''||IMK.NEXTVAL||'</NS1:MESSAGEID>'                          
--------------------------------------------------------------------------------
<ns1:messageId> 47</ns1:messageId>                                              
 
SQL> spool off

As I want to have only the marked in red I tried to turn off some of the settings
set termout off
set echo off
set verify off
set heading off
but it seems it didnt take effect.

bmk · July 5, 2012, 5:39am

How your are populating the data into xml tags,where it coming from...

---------- Post updated at 04:39 AM ---------- Previous update was at 04:23 AM ----------

You are excepting this one... From the table i am spooling like...

<ns1:message>
 <ns1:messageId>7566</ns1:messageId> 
 <ns1:languageCode>JONES</ns1:languageCode>
</ns1:message>
<ns1:message>
 <ns1:messageId>7698</ns1:messageId> 
 <ns1:languageCode>BLAKE</ns1:languageCode>
</ns1:message>

zb99 · July 5, 2012, 6:30am

I am not sure I understand your question at all but... the data which is populated is two types:

is results of queries which come directly from the oracle database
is fixed values.

I am expecting this one:

<?xml version="1.0" encoding="UTF-8"?>
<ns1:messageId> 47</ns1:messageId>

Where <?xml version="1.0" encoding="UTF-8"?> is fixed text which I just want to store in the file and <ns1:messageId> 47</ns1:messageId> is the result of the following select: SELECT '<ns1:messageId>' || ' ' || imk.nextval || '</ns1:messageId>' FROM dual;

bmk · July 5, 2012, 7:09am

Not clear...
why are you using select statement here..What is your input data and output data

zb99 · July 5, 2012, 7:26am

Because I need to select a sequence or a value from the database. Instead

 SELECT '<ns1:messageId>' || ' ' || imk.nextval || '</ns1:messageId>'  FROM dual;

it could be

select value from table_name;

What do you suggest to use instead of the select statement?

bmk · July 5, 2012, 8:22am

i am asking "What is your input data and output data"

---------- Post updated at 07:22 AM ---------- Previous update was at 07:00 AM ----------

you are excepting this one...

 SELECT '<?xml version="1.0" encoding="UTF-8"?>'||chr(10) || '<ns1:messageId>' || ' ' || CATALOGS_SEQ.nextval || '</ns1:messageId>' FROM dual;

zb99 · July 6, 2012, 4:04am

I use the following:

set pagesize 0;
set serveroutput on
set termout off       
set verify off     
set heading off    
set long 999
set lines 999
SET FEEDBACK OFF
SET HEAD OFF

SELECT '<?xml version="1.0" encoding="UTF-8"?>' || chr(10) ||
       '<!--Sample XML file generated by XMLSpy v2010 rel. 2 (http://www.altova.com)-->' || chr(10) ||
       '<HarpeML_CBS_IMX_ExchangeRate_Flow  xsi:noNamespaceSchemaLocation="HarpeML_CBS_IMX_ExchangeRate(REF-IMX-1)_v0.0.00.xsd"  xmlns:harpeml="http://www.harpeml.com"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'||chr(10)||
       '<Header>'||chr(10)||
       '<harpeml:technicalIndicator>'||'HDR'||'</harpeml:technicalIndicator>'||chr(10)||
       '<harpeml:orderNumber>'||''||lpad ( imk.nextval, 10, '0' )||'</harpeml:orderNumber>'||chr(10)||
       '<harpeml:dataSelectionDate>'||''||to_char(sysdate -  4,'DD/MM/YYYY')||''||'</harpeml:dataSelectionDate>'||chr(10)|| 
        '<harpeml:extractionTimeStamp>'||''||CURRENT_TIMESTAMP||''||'</harpeml:extractionTimeStamp>'||chr(50)||'</Header>'
        FROM dual;
SELECT '<ExchangeRates>'||chr(10)||'<ExchangeRate>'||chr(10)||
       '<harpeml:technicalIndicator>'||'02'||'</harpeml:technicalIndicator>'||chr(10)||
       '<harpeml:currencyExchangeRateType>'||''||'D'||'</harpeml:currencyExchangeRateType>'||chr(10)
        FROM dual;
SELECT  '<harpeml:baseCurrencyCode>'||abrev||'</harpeml:baseCurrencyCode>'  FROM(SELECT abrev FROM v_domaine where type = 'DEVISE' ORDER BY  dbms_random.normal)WHERE rownum = 1;
SELECT 
        '<harpeml:counterCurrencyCode>'||abrev||'</harpeml:counterCurrencyCode>'FROM(SELECT  abrev FROM v_domaine where type = 'DEVISE' ORDER BY  dbms_random.normal)WHERE rownum = 1;
SELECT 
        '<harpeml:startValidityDate>'||''||to_char(sysdate -  4,'DD/MM/YYYY')||''||'<harpeml:startValidityDate>'||chr(10)||
         '<harpeml:countryCode>'||abrev||'</harpeml:countryCode>'FROM(SELECT  abrev FROM v_domaine where type = 'pays' ORDER BY  dbms_random.normal)WHERE rownum = 1;   
SELECT
        '<harpeml:exchangeRate>'||rpad ( imk.nextval, 3, '51' )||'</harpeml:exchangeRate>'||chr(10)||
        '<harpeml:appreciationOrDepreciationReport>'||'1'||'</harpeml:appreciationOrDepreciationReport>'||chr(10)||
        '<harpeml:dataSourceSystem>'||'freetext'||'</harpeml:dataSourceSystem>'||chr(10)||
        '</ExchangeRate>'||chr(10)||
        '</ExchangeRates>'||chr(10)||
        '</HarpeML_CBS_IMX_ExchangeRate_Flow>'
        from dual;
/

This is the result:

<?xml version="1.0" encoding="UTF-8"?>
<!--Sample XML file generated by XMLSpy v2010 rel. 2 (http://www.altova.com)-->
<HarpeML_CBS_IMX_ExchangeRate_Flow  xsi:noNamespaceSchemaLocation="HarpeML_CBS_IMX_ExchangeRate(REF-IMX-1)_v0.0.00.xsd"  xmlns:harpeml="http://www.harpeml.com"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Header>
<harpeml:technicalIndicator>HDR</harpeml:technicalIndicator>
<harpeml:orderNumber>0000000161</harpeml:orderNumber>
<harpeml:dataSelectionDate>02/07/2012</harpeml:dataSelectionDate>
<harpeml:extractionTimeStamp>06-JUL-12 09.29.01.812631 AM +03:00</harpeml:extractionTimeStamp>2</Header>

<ExchangeRates>
<ExchangeRate>
<harpeml:technicalIndicator>02</harpeml:technicalIndicator>
<harpeml:currencyExchangeRateType>D</harpeml:currencyExchangeRateType>

<harpeml:baseCurrencyCode>BOB</harpeml:baseCurrencyCode>
<harpeml:counterCurrencyCode>SGD</harpeml:counterCurrencyCode>
<harpeml:startValidityDate>02/07/2012<harpeml:startValidityDate>
<harpeml:countryCode>BDI</harpeml:countryCode>

<harpeml:exchangeRate>162</harpeml:exchangeRate>
<harpeml:appreciationOrDepreciationReport>1</harpeml:appreciationOrDepreciationReport>
<harpeml:dataSourceSystem>freetext</harpeml:dataSourceSystem>
</ExchangeRate>
</ExchangeRates>
</HarpeML_CBS_IMX_ExchangeRate_Flow>

<harpeml:exchangeRate>163</harpeml:exchangeRate>
<harpeml:appreciationOrDepreciationReport>1</harpeml:appreciationOrDepreciationReport>
<harpeml:dataSourceSystem>freetext</harpeml:dataSourceSystem>
</ExchangeRate>
</ExchangeRates>
</HarpeML_CBS_IMX_ExchangeRate_Flow>

Does anyone have idea why the last block appears two times? And could you give any suggestion how to remove the empty lines from the output file?

---------- Post updated at 11:04 AM ---------- Previous update was at 09:36 AM ----------

The problem with the repeating blcok is resolved. It was cause by '/' at the end of the script instead it should be 'exit', so only empty lines left.

bmk · July 7, 2012, 2:44am

Try run in sqlpus or TOAD. Append all sql result data into one variable try...

declare 

v_message varchar2(32767);
v_message1 varchar2(32767);

begin

SELECT '<?xml version="1.0" encoding="UTF-8"?>' || chr(10) ||
       '<!--Sample XML file generated by XMLSpy v2010 rel. 2 (http://www.altova.com)-->' || chr(10) ||
       '<HarpeML_CBS_IMX_ExchangeRate_Flow  xsi:noNamespaceSchemaLocation="HarpeML_CBS_IMX_ExchangeRate(REF-IMX-1)_v0.0.00.xsd"  xmlns:harpeml="http://www.harpeml.com"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'||chr(10)||
       '<Header>'||chr(10)||
       '<harpeml:technicalIndicator>'||'HDR'||'</harpeml:technicalIndicator>'||chr(10)||
       '<harpeml:orderNumber>'||''||lpad ( imk.nextval, 10, '0' )||'</harpeml:orderNumber>'||chr(10)||
       '<harpeml:dataSelectionDate>'||''||to_char(sysdate -  4,'DD/MM/YYYY')||''||'</harpeml:dataSelectionDate>'||chr(10)|| 
        '<harpeml:extractionTimeStamp>'||''||CURRENT_TIMESTAMP||''||'</harpeml:extractionTimeStamp>'||chr(50)||'</Header>'
        FROM into v_message1 dual;
 
 v_message := v_message || v_message1;

SELECT '<ExchangeRates>'||chr(10)||'<ExchangeRate>'||chr(10)||
       '<harpeml:technicalIndicator>'||'02'||'</harpeml:technicalIndicator>'||chr(10)||
       '<harpeml:currencyExchangeRateType>'||''||'D'||'</harpeml:currencyExchangeRateType>'||chr(10)
        FROM into v_message1 dual;

v_message := v_message || v_message1;

SELECT  '<harpeml:baseCurrencyCode>'||abrev||'</harpeml:baseCurrencyCode>' into v_message1 FROM(SELECT abrev FROM v_domaine where type = 'DEVISE' ORDER BY  dbms_random.normal)WHERE rownum = 1 ;

v_message := v_message || v_message1;

SELECT 
        '<harpeml:counterCurrencyCode>'||abrev||'</harpeml:counterCurrencyCode>' into v_message1 FROM(SELECT  abrev FROM v_domaine where type = 'DEVISE' ORDER BY  dbms_random.normal)WHERE rownum = 1;

v_message := v_message || v_message1;

SELECT 
        '<harpeml:startValidityDate>'||''||to_char(sysdate -  4,'DD/MM/YYYY')||''||'<harpeml:startValidityDate>'||chr(10)||
         '<harpeml:countryCode>'||abrev||'</harpeml:countryCode>' into v_message1 FROM(SELECT  abrev FROM v_domaine where type = 'pays' ORDER BY  dbms_random.normal)WHERE rownum = 1;   

v_message := v_message || v_message1;

SELECT
        '<harpeml:exchangeRate>'||rpad ( imk.nextval, 3, '51' )||'</harpeml:exchangeRate>'||chr(10)||
        '<harpeml:appreciationOrDepreciationReport>'||'1'||'</harpeml:appreciationOrDepreciationReport>'||chr(10)||
        '<harpeml:dataSourceSystem>'||'freetext'||'</harpeml:dataSourceSystem>'||chr(10)||
        '</ExchangeRate>'||chr(10)||
        '</ExchangeRates>'||chr(10)||
        '</HarpeML_CBS_IMX_ExchangeRate_Flow>'
        from  into v_message1 dual;

v_message := v_message || v_message1;

dbms_output.put_line(v_message);

end;
/

zb99 · July 9, 2012, 2:01am

It is working.