OraFAQ Forum: SQL & PL/SQL » What should be the XML format for this code.

Home » SQL & PL/SQL » SQL & PL/SQL » What should be the XML format for this code. (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

What should be the XML format for this code. [message #602619]

Fri, 06 December 2013 00:51

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi Experts,

For the below code what should be the XML format.

 
select                 cast(extractValue(value(i), '*/@v') as varchar2 (25 char)) as V_VALUE
                     , cast(extractValue(value(i), '*/@s') as varchar2 ( 5 char)) as SID
                     , cast(extractValue(value(i), '*/@m') as number            ) as MTIME
                  from table(XmlSequence(extract(XmlType(p_xml_text), '/l/i'))) i
 
 
I have tried this but not working.
 
 
'<l>
<i>
<v="C4567892" s="333" m="50" />
<v="C3245986" s="5556" m="50" />
</i>
</l>'

Please provide the correct XML message format for the above code.

Thanks.

Report message to a moderator

Re: What should be the XML format for this code. [message #602624 is a reply to message #602619]

Fri, 06 December 2013 01:21

_jum
Messages: 577
Registered: February 2008

Senior Member

The text is no valid XML. Start with:

WITH xml_text AS
 (SELECT  
   '<l>
      <i>
        <row v="C4567892" s="333"  m="50"/>
        <row v="C3245986" s="5556" m="50"/>
     </i>
   </l>' p_xml_text FROM dual)
SELECT  
      EXTRACTVALUE(VALUE(i), '*/@v')  V_VALUE,
      EXTRACTVALUE(VALUE(i), '*/@s')  SID,
      EXTRACTVALUE(VALUE(i), '*/@m')  MTIME
  FROM xml_text,      
      TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE(p_xml_text), '/l/i/*'))) i;

V_VALUE		SID	MTIME     
--------------------------------------------------------------------------------
C4567892	333	50
C3245986	5556	50

Btw. you could/should use XMLTABLE instead.

[Updated on: Fri, 06 December 2013 01:25]

Report message to a moderator

Re: What should be the XML format for this code. [message #602739 is a reply to message #602619]

Sun, 08 December 2013 05:32

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

Thanks for your code.
How to get sequence unique number for each record.
What is the best approach XMLTABLE or this.
Please help me.

Thanks.

Report message to a moderator

Re: What should be the XML format for this code. [message #602742 is a reply to message #602739]

Sun, 08 December 2013 05:49

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

XMLTABLE

Report message to a moderator

Re: What should be the XML format for this code. [message #602746 is a reply to message #602742]

Sun, 08 December 2013 06:23

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi ,

For XMLTABLE I have tried the below but not displaying any data.

WITH xml_text AS
(SELECT
'<l>
<i>
<row v="C4567892" s="333" m="50"/>
<row v="C3245986" s="5556" m="50"/>
</i>
</l>' p_xml_text FROM dual)
select v,s,m
from xml_text, xmltable('/l/i' passing XMLTYPE(p_xml_text)
columns v VARCHAR2(10) path '/@v'
,s NUMBER path '/@s'
, m NUMBER path '/@m'
);
And also how to get unique sequence number for each record.

Please help me.

Thanks.

Report message to a moderator

Re: What should be the XML format for this code. [message #602748 is a reply to message #602746]

Sun, 08 December 2013 06:40

ramya_162
Messages: 107
Registered: August 2013
Location: Banglore

Senior Member

Hi,

I got it.Can you please help me on getting unique number for each record.

I want the output as below.

V_VALUE		SID	MTIME  id   
-------------------------------------
C4567892	333	50      1
C3245986	5556	50      2

Please help me.

Thanks.

Report message to a moderator

Re: What should be the XML format for this code. [message #602750 is a reply to message #602748]

Sun, 08 December 2013 07:19

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

ROWNUM.

SY.

Report message to a moderator

Re: What should be the XML format for this code. [message #602780 is a reply to message #602750]

Mon, 09 December 2013 01:26

_jum
Messages: 577
Registered: February 2008

Senior Member

Alternatively you can use a FOR ORDINALITY clause in the XMLTABLE to generate row numbers.

Report message to a moderator

Previous Topic:	Mutating Error
Next Topic:	Insert value from one table to 2nd table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 23:54:20 CDT 2024