Script which fill data in XML file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script which fill data in XML file
# 1  
Old 07-04-2012
Data Script which fill data in XML file

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:
Code:
<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.

Code:
#!/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?
# 2  
Old 07-04-2012
You could try lots of SELECT CONCAT("string", col, "string", col, ...) to assemble the text you want.
This User Gave Thanks to Corona688 For This Post:
# 3  
Old 07-05-2012
Thanks for the good idea.

I will use CONCAT to export the values from the database + tags. Like this:
Code:
SELECT '<ns1:messageId>' || ' ' || seq.nextval || '</ns1:messageId>'  FROM dual;

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

Code:
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:
Code:
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.

Last edited by zb99; 07-05-2012 at 06:14 AM..
# 4  
Old 07-05-2012
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...

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

# 5  
Old 07-05-2012
I am not sure I understand your question at all but... the data which is populated is two types:
1. is results of queries which come directly from the oracle database
2. 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;

Last edited by zb99; 07-05-2012 at 07:37 AM..
# 6  
Old 07-05-2012
Not clear...
why are you using select statement here..What is your input data and output data
# 7  
Old 07-05-2012
Because I need to select a sequence or a value from the database. Instead
Code:
 SELECT '<ns1:messageId>' || ' ' || imk.nextval || '</ns1:messageId>'  FROM dual;

it could be
Code:
select value from table_name;

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

Last edited by Franklin52; 07-06-2012 at 11:14 AM.. Reason: Please use code tags for data and code samples, thank you
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