Xml Oracle:Multiple Child Node Extract

XML Oracle : Multiple Child Node extract

You can achieve desired result by using XMLTable() function:

select q.Lastname
     , q.Numberid
     , s.codeid
     , w.LoginId
     , q.address
  from t1 t
  left join xmltable('/begin/entry'
                      passing t.xml_col 
                      columns LastName   varchar2(21)  path 'lastname',
                              NumberId   number        path 'NumberList/number',
                              Address    varchar2(201) path 'address/addresslist',
                              CodeList   XmlType       Path 'NumberList/codelist/code',
                              Logins     XmlType       Path 'NumberList/login/user'
                      ) q
    on (1=1) 
  left join xmltable('/code'
                      passing q.CodeList
                      columns CodeId number path '.') s
    on (1=1)
  left join   xmltable('/user'
                        passing q.Logins
                        columns LoginId varchar2(11) path '.') w
    on (1=1)

Result: SQLFiddle Demo

Lastname Numberid Codeid Loginid Address 
---------------------------------------------------------------------------
gordon   100      213    user1   Jl. jalan pelan-pelan ke Bekasi, Indonesia 
gordon   100      213    user2   Jl. jalan pelan-pelan ke Bekasi, Indonesia 
gordon   100      214    user1   Jl. jalan pelan-pelan ke Bekasi, Indonesia 
gordon   100      214    user2   Jl. jalan pelan-pelan ke Bekasi, Indonesia 
mark     null     null   null    Jl. jalan cepet-cepet ke Jakarta, Indonesia 

Find out more about XMLTable() function.

Note: Working with Oracle releases prior to 11.2.0.2, you can encounter ORA-1780 error(bug 8545377) on certain types of XML queries when cursor_sharing parameter is set to FORCE or SIMILAR(deprecated starting from 11.2). Setting cursor_sharing parameter to EXACT(default value), will solve the problem.

Oracle XML Query with multiple child nodes

Try using string-join function: 'string-join(Values//Value, ", ")'.

XML Oracle: Extract specific attribute from multiple repeating child nodes

Aaand I managed to find the solution, which is quite simple, just added [text()="Form3"]/.../" to predicate the Xpath as in

SELECT         
ExtractValue(Value(p),'/Customer/Loyalty/Client/Identifications/Identification/*[local-name()="Form"][text()="Form3"]/text()') as form, 
 ExtractValue(Value(p),'/Customer/Loyalty/Client/Identifications/Identification/Form[text()="Form3"]/.../*[local-name()="value"]/text()') as value

Also extracted the values just sending them directly into the procedure's OUT parameter:

P_FORM := r.form;
P_LOYALTY_VALUE := r.value;

Extract specific xml node from duplicate ones in oracle

You want to select the node that has the ADI text as first string.

Try this:

select 
    t.my_xml.extract('//city[./string[1]/text() = "ADI"]/string[2]/text()').getStringVal()
from XYZ t
    where t.my_xml.existsNode('/India/city[string[1] = "ADI"]') = 1;

Oracle - Extract XML nodes values into a list with semicolon as separators

You can use:

SELECT LISTAGG(x.value, ';') WITHIN GROUP (ORDER BY ROWNUM) AS list
FROM   table_name t
       CROSS JOIN XMLTABLE(
         '/Parameter/Values/Value'
         PASSING XMLTYPE(t.xml)
         COLUMNS
           value VARCHAR2(3) PATH './text()'
       ) x

Which, for your sample data:

CREATE TABLE table_name ( xml ) AS
SELECT '<Parameter>
    <Values>
        <Value>abc</Value>
        <Value>def</Value>
        <Value>ghi</Value>
    </Values>
</Parameter>' FROM DUAL;

Which outputs:

LIST
abc;def;ghi

How to extract value of all the child nodes of a specific node (provided through input parameter) from XMLType Column in Oracle

You could manipulate the XML inside an XPath with FLWOR syntax, but you could also use XMLTable to extract all the values; or rather, two XMLTables, one for a singleton element type and a second optional one for array expansion; get all the values as strings; and aggregate the results together:

select a.id,
  listagg(coalesce(x1.value, x2.value), ' ')
    within group (order by coalesce(x1.n, x2.n)) as result
from a
cross apply xmltable (
  '(/*[local-name()=$var1][1])'
  passing xmltype(col1), 'ConstantInputProperties' as "var1"
  columns
    n for ordinality,
    value varchar2(30) path 'Value[@xsi:type="xsd:int"]',
    array xmltype path 'Value[fn:starts-with(@xsi:type, "ArrayOf")]'
) x1
outer apply xmltable (
  'Value/*'
  passing array
  columns
    n for ordinality,
    value varchar2(30) path '.'
) x2
group by a.id;

ID | RESULT                  
-: | :-----------------------
 1 | 0                       
 2 | 0 1                     
 3 | true true true true true
 4 | 1.0000000000       

db<>fiddle

The n for ordinality just gives a numeric value that lets you keep the original sub-element order when aggregating (so you get 0 1 and not 1 0), If you don't want a spaces added to to the aggregated value then just change the second listagg argument from ' ' to null, though then you can't sell the difference between a singleton 10 and a pair of values with 1 and 0, so that doesn't seem very useful - not that an aggregated value seems that useful anyway really.

You could split into multiple sub-XMLTables, but that's probably not going to gain you anything here; db<>fiddle for info though.

---

can you suggest how to pass the ConstantInputProperties value as an argument and use it as a variable in function input in this case EXTRACT(XMLTYPE(col1), '(/[local-name()="ConstantInputProperties"]//text())')

The extract() function is deprecated. Use XMLQuery instead; for example:

select xmlquery(
  '(/*[local-name()=$var1][1])/Value/text()'
  passing xmltype(col1), 'ConstantInputProperties' as "var1"
  returning content)
from a

How to get all the child nodes of an xml data based on parent node in Oracle PLSQL

EXTRACT is deprecated. Use XMLQUERY (or XMLTABLE):

SELECT XMLQUERY(
         '//GrandParent/*'
         PASSING XMLTYPE(value)
         RETURNING CONTENT
       ).getClobVal() AS child_nodes
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (value CLOB);

INSERT INTO table_name (value) VALUES (
  '<GrandGrandParent>
    <GrandParent>
        <Parent1>
            <Child1>ABC</Child1>
            <Child2>DEF</Child2>
        </Parent1>
        <Parent2>
            <Child1>GHI</Child1>
            <Child2>JKL</Child2>
            <Child3>
                <Child1>MNO</Child1>
                <Child2>PQR</Child2>
            </Child3>
        </Parent2>
    </GrandParent>
</GrandGrandParent>'
);

1 rows affected

Outputs:

CHILD_NODES
<Parent1><Child1>ABC</Child1><Child2>DEF</Child2></Parent1><Parent2><Child1>GHI</Child1><Child2>JKL</Child2><Child3><Child1>MNO</Child1><Child2>PQR</Child2></Child3></Parent2>

---

Related Topics

Google Bigquery Case Function

Multiple Constraints in Table: How to Get All Violations

Sqlite Like % and _

What SQLite Column Name Can Be/Cannot Be

Can SQL Server SQL_Latin1_General_Cp1_Ci_As Be Safely Converted to Latin1_General_Ci_As

Behaviour of Not Like with Null Values

Oracle Replace() Function Isn't Handling Carriage-Returns & Line-Feeds

How to Include the Total Number of Returned Rows in the Resultset from Select T-SQL Command

Spark SQL Converting String to Timestamp

SQL Server Trim Character

How to Write Union in Doctrine 2.0

Display Parent-Child Relationship When Parent and Child Are Stored in Same Table

MySQL Query to Dynamically Convert Row Data to Columns

Exporting Blob from MySQL Database to File with Only SQL

Comparing Results with Today's Date

Sql: Last_Value() Returns Wrong Result (But First_Value() Works Fine)

MySQL Question - How to Handle Multiple Types of Users - One Table or Multiple

How to Catch a Query Exception in Laravel to See If It Fails

---

Leave a reply

Submit