0

I have data in a row for column TEST1 (Table Name: TableTest) like 

<n0:RouterName Value="ST_APOP"/>
<n0:ExtZone/>
<n0:SalesOrders>
<n0:SalesOrder ValidationResult="SUCCESS">
<n0:SalesOrderID Value="4F47N006800000_0261"/>

I need to select 4F47N006800000_0261 value in my select query. How to trim that.

Rahul
  • 29
  • 1
  • 10

2 Answers2

0

Your XML is not valid - so, lets add closing tags:

CREATE TABLE your_table ( xml ) AS
SELECT '<n0:RouterName Value="ST_APOP"/>
<n0:ExtZone/>
<n0:SalesOrders>
<n0:SalesOrder ValidationResult="SUCCESS">
<n0:SalesOrderID Value="4F47N006800000_0261"/>
</n0:SalesOrder>
</n0:SalesOrders>' FROM DUAL

Query:

SELECT SalesOrderId
FROM   your_table t,
       XMLTABLE(
         XMLNAMESPACES( 'http://your.server/namespaces/n0' AS "n0" ),
         '//root/n0:SalesOrders/n0:SalesOrder'
         PASSING XMLTYPE(
                   '<root xmlns:n0="http://your.server/namespaces/n0">'
                   || t.xml
                   || '</root>'
                 )
         COLUMNS SalesOrderId VARCHAR2(100) PATH '//n0:SalesOrderID/@Value'
       );

Output:

SALESORDERID
-------------------
4F47N006800000_0261

db<>fiddle here

MT0
  • 143,790
  • 11
  • 59
  • 117
-2

try this:

select   
substr(column_name,instr(column_name,'Value',1,2)+7,length(substr(column_name,instr(column_name,'Value',1,2)+7))-3)
 from
test1
CompEng
  • 7,161
  • 16
  • 68
  • 122
  • What happens if the XML is in a different order? – MT0 May 22 '17 at 09:14
  • 2
    Attempt to manipulate XML using string manipulation will end in tears (or unnecessary questions on sites like stackoverflow). Please remember XML is not a string of characters containing some apparently non random < and > characters, it is a complex object model which can occasionally be represented as a string of characters with non random < and > for the purposes of human consumption – mark d drake May 22 '17 at 21:53
  • @MT0 in some cases, the xml is always same order because the file is created by machines like gate control machine or something like that , so the answer is good for this question – CompEng Oct 16 '19 at 10:52
  • What happens if the data includes `` so the second instance of the `Value` sub-string is a completely different place in the XML hierarchy? Or if can have optional attributes/elements at the end of the file so you cannot just chop the last 3 characters from it? Trying to parse arbitrary XML with string functions (or [regular expressions](https://stackoverflow.com/q/1732348/1509264)) is not going to end well; if it its strictly controlled XML with defined attribute/element values then it may be possible but all it takes it one change and the query doesn't work. – MT0 Oct 16 '19 at 11:01
  • last 3 chars are : "/> so they do not changed – CompEng Oct 16 '19 at 12:15
  • But if the end of the XML is `` or `` or even just putting a space in `` then your query is not going to give the correct value as it doesn't detect the end of the attribute instead just naively returns the remaining sub-string up to the last 3 characters. As I said, you need to have strict requirements on the structure of the XML, that you cannot deviate from, otherwise extracting sub-strings will fail. – MT0 Oct 16 '19 at 12:41