Extracting values from an XML string using SQL query in oracle database

There are two methods you can use for extracting values from XML string (a column in a table). The column type will be XMLType.

These methods are EXTRACT(columnName, xpath). This returns list of nodes, also a record if extracting with a unique filter value.
You can also use ExtractValue(columnName, xpath) - This will work only for a single node, not for a collection of nodes. This way you can retrieve value from a unique record.


See examples below:

<Person>
    <Name>Test</Name>
    <Address>
        <Street Number=1345>Monroe St</Street>
        <City>TestCity</City>
        <Zip>12345</Zip>
    </Address>
</Person>

Assume this xml data in column
You can extract the street number attribute value using the query as below:

This can return multiple records:
select extract(columnName,'/Person/Address/Street/@Number') as StreetNumber from Table1;

For returning single record:
select extract(columnName,'/Person/Address/Street/@Number') as StreetNumber from Table1 where ID = 200;

Also For returning single record only:
select extractvalue(columnName, '/Person/Address/Street/@Number') as StreetNumber from Table1 where ID = 200;

I have used this and works like a charm.
Hope this finds useful to you also.
Njoyy...

Query to Find second highest/Nth highest salary in SQL Server and Oracle

SQL Sever
========
select top 1 a.* from (
select top 4 salary from table1 order by salary DESC ) a order by a.salary ASC;

Now in generic terms... Lets say for Nth salary.

select top 1 a.* from (
select top N salary from table1 order by salary DESC ) a order by a.salary ASC;

Just replace N with the number you want to find the salary. (N = 1 for first, N =2 for second, N= 3 for third.. so on..).

Oracle
======
select * from (
select tb1.*, rownum Rnum from (
select * from table1 order by salary DESC) tb1 where rownum <= 2) where Rnum >= 2;

Now to make it generic.. Lets say for Nth salary.

select * from (
select tb1.*, rownum Rnum from (
select * from table1 order by salary DESC) tb1 where rownum <= N) where Rnum >= N;

Just replace N with the number you want to find the salary. (N = 1 for first, N =2 for second, N= 3 for third.. so on..).

How to write a left outer join in LINQ?

This can be accomplished by using DefaultIfEmpty(). See the example LINQ query below:

var provQuery = (from a in entities.Table1
                join c in entities.Table2 on a.ID equals c.ID into temppvcon
                from t2 in temppvcon.DefaultIfEmpty()
                where a.ID == Id
                select new { a.ID, a.NAME, t2.ID, t2.NAME });

Check out the SQL generated from the above LINQ query:

SELECT
1 AS "C1",
"Extent1"."ID" AS "ID",
"Extent1"."NAME" AS "NAME",
"Extent2"."ID" AS "ID",
"Extent2"."NAME" AS "NAME"
FROM  (SELECT
      "TABLE1"."ID" AS "ID",
      "TABLE1"."CON_ID" AS "CON_ID",
      "TABLE1"."NAME" AS "NAME",
      "TABLE1"."ADDED_DT" AS "ADDED_DT"
      FROM "DB"."TABLE1" "TABLE1") "Extent1"
LEFT OUTER JOIN (SELECT
      "TABEL2"."NAME" AS "NAME",
      "TABEL2"."PART" AS "PART",
      "TABEL2"."ID" AS "ID",
      "TABEL2"."CLUSTER_ID" AS "CLUSTER_ID"
      FROM "CDCDATA"."TABEL2" "TABEL2") "Extent2" ON "Extent1"."ID" = "Extent2"."ID"
WHERE ("Extent1"."ID" = :p__linq__0)

Hope this is clear. Enjoy...

How to view the SQL generated by LINQ to Entities?

There is a ToTraceString() method for objectQuery. The anonymous object returned from the LINQ query has to be casted into ObjectQuery as shown below to use the ToTraceString():

var provQuery = from a in entities.Table1 where a.ID == id orderby a.Name
                            select a;

The SQL generated from the LINQ can be viewed by using as below:

((System.Data.Objects.ObjectQuery)provQuery).ToTraceString()

You can copy from watch window and run the sql query to test it.