Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Script which fill data in XML file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script which fill data in XML file
# 8  
Old 07-05-2012
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...
Code:
 SELECT '<?xml version="1.0" encoding="UTF-8"?>'||chr(10) || '<ns1:messageId>' || ' ' || CATALOGS_SEQ.nextval || '</ns1:messageId>' FROM dual;

This User Gave Thanks to bmk For This Post:
zb99
# 9  
Old 07-06-2012
I use the following:
Quote:
1 - Put your queries in a text file like so:
set pagesize 0;

select another_field
from another_table;
/

2 - Save it somewhere (let's say c:\my_file.sql)

3 - Run this at the command prompt:

c:\>sqlplus -s username/password@database.domain.com < tmp.sql > output.txt

4 - Look inside "output.txt"
Code:
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:
Code:
<?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.
# 10  
Old 07-07-2012
Try run in sqlpus or TOAD. Append all sql result data into one variable try...

Code:
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;
/

# 11  
Old 07-09-2012
It is working.
Last edited by zb99; 07-10-2012 at 10:15 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Need get data from XML file through shell script..

hi all, here is the sample log file and these errors are repeated in log file.. i need all the repeated time stamp ,severity and message tags needs to print in output file.. through shell script <log-message> <timestamp>2019-03-13T04:52:49.648-05:00</timestamp> <severity>ERROR</severity>... (17 Replies)
Discussion started by: ravi
17 Replies

2. Shell Programming and Scripting

Fill data in column with previous value

Gents, Kindly help me. I have a file with empty values in selected column, I will like to fill the empty values with the previous value. Example Input file X 4959 30010 66727.00 20457.001 1 1441 66512.00 20234.00 20520.001 X 4959 30010 66727.00 20457.001 145 ... (7 Replies)
Discussion started by: jiam912
7 Replies

3. Shell Programming and Scripting

Fill data if number range is given

Hi I want to get all numbers if number range is given as input. Eg: INPUT FILE 100-105 107 108-112 OUTPUT REQUIRED: 100 101 102 103 104 105 107 108 109 110 111 112 How can I do it using shell? :confused: Thanks in advance. (11 Replies)
Discussion started by: dashing201
11 Replies

4. Emergency UNIX and Linux Support

Script to fill the file system mount with empty files to desired size

We are regularly using for our testing, where we are manually filling up the mount with desired size with following command dd if=/dev/zero of=file_2GB bs=2048000 count=2000 We are planning to automate the task where taking input for % of size as one input and the name of the file system... (8 Replies)
Discussion started by: chandu123
8 Replies

5. Shell Programming and Scripting

Fill in missing Data

hello everyone, I have a task to input missing data into a file. example of my data below: Wed Feb 01 09:00:02 EST 2012,,,0.4,0.3,,0.3,,0.3,,0.5,,0.3,,,0.4,0.3, Wed Feb 01 09:00:11 EST 2012,,,,,,,0.2,,,,,,,,,, Wed Feb 01 09:00:22 EST... (23 Replies)
Discussion started by: Nolph
23 Replies

6. Shell Programming and Scripting

fill in last column of data

Hello, I am fairly new to awk, and I have the following problem. My file has missing data in the last column, and the program I am pre-processing this file for cannot interpret correctly shortened rows (it just wraps the data around). Is there a way to force awk to create the same... (6 Replies)
Discussion started by: timert34
6 Replies

7. Shell Programming and Scripting

How to fill data from other file and get some output

Greetings, I have a hard time creating a large number of user profiles in a database. The data file looks like this : 01/01/80 Mitch Conley . . . . And I need to put the output into: Name: Mitch Surname: Conley Birthday: 01/01/80 Thanks in advance! (3 Replies)
Discussion started by: hemo21
3 Replies

8. Shell Programming and Scripting

Convert XML to Data File in Shell Script

Hi All, I will be getting a huge XML file with a lot of records in it. I need to convert it into multiple data files. SAMPLE XML FILE <ABSProductCatalog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> - <ProductSalesHierachy> - <Portfolios> - <Portfolio productCode="P1"> ... (8 Replies)
Discussion started by: ragha81
8 Replies

9. Shell Programming and Scripting

Help with shell script to extract data from XML file

Hello Scripting Gurus, I need help with extracting data from the XML file using shell script. The data is in a large XML and I need to extract the id values of all completedworkflows. Here is a sample of it. Input and output data is also in the attached text files. <wfregistry>... (5 Replies)
Discussion started by: yajaykumar
5 Replies

10. Shell Programming and Scripting

Parsing and getting data from XML file using ksh script

Hi All, I have a xml file for example as described below <xml> <address> <street><street> <address/> <isbn>426728783932020308393930303</isbn> <book> <name> </name> </book> . . . </xml> My problem is to get the isbn number from the above described file using ksh script. Could... (6 Replies)
Discussion started by: vinna
6 Replies
Login or Register to Ask a Question