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

‘MyEntities’: type used in a using statement must be implicitly convertible to ‘System.IDisposable’

‘MyEntities’: type used in a using statement must be implicitly convertible to ‘System.IDisposable’

When trying to access the entity object which is in a different project, I added the reference of the project having the entity object to the new project and was trying to use the code block as below:

 using (Entities entities = new Entities(entityConnStr))
{

}

This gave me the error. The solution for this was to add a reference to 'System.Data.Entity' in the new project. After this it started working for me.

Third party WCF Web Service files (if your dev machine doesn't have access to the url of the web service) - getting WSDL - SVCUtil.exe

Run SVCUtil.exe from Visual Studio command prompt, with the url of the third party web service from a computer where you have access to the third party web service.


It will generate a .CS and .CONFIG file. The .CS file has all the method, parameter and return value information. The .CONFIG file has all the end point details.

Add the .CS file to the client project and add the details from the .CONFIG to the client config file.

Now you can access the third party web service (if your dev machine doesn't have access to the url of the web service). This is just a tip.

Creating Custom Properties for/extending the Entity framework generated class

How to create a custom property for an entity framework generated class. I had a requirement and the way I did was to create a partial class for the entity generated class. After creating the partial class, I added a property and was able to access the property in the same project.

BUT here comes problem.... This entity generated class was in a web service. After I deployed my web service and tried to test with a client application, I was not able to access the newly added custom property.


public partial class ClassA                       //original class, entity generated
    {
        public string Name { get; set; }
      
        [EdmScalarPropertyAttribute(EntityKeyProperty=false, IsNullable=true)]
        [DataMemberAttribute()]
        public global::System.String FROM_USER
        {
            get
            {
                return _FROM_USER;
            }
            set
            {
                OnFROM_USERChanging(value);
                ReportPropertyChanging("FROM_USER");
                _FROM_USER = StructuralObject.SetValidValue(value, true);
                ReportPropertyChanged("FROM_USER");
                OnFROM_USERChanged();
            }
        }
        private global::System.String _FROM_USER;
        partial void OnFROM_USERChanging(global::System.String value);
        partial void OnFROM_USERChanged();
    }

public partial class ClassA                      //newly created partial class for the above class
    {
        public int ID { get; set; }
    }

 The above partial class didn't work for me. So I had to add an extra attribute for the new property as below:

public partial class ClassA
    {
        [global::System.Runtime.Serialization.DataMemberAttribute()]
        public int ID { get; set; }
    }

 This solution worked for me. Hope this tip helps you.

ERROR - Keyset does not exist - When a web site hosted in IIS, have a certificate installed in server.

[FaultException`1: Keyset does not exist

]

   System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) +9442991

   System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) +345

I was accessing a WCF web service from a web application hosted in IIS. The call to the external web service is validated with a certificate installed in server. This certificate should match with the certificate in the third party side. After successful validation, we get the output.

But I was getting error as mentioned above. It was something related to the certificate. Finally I found out that the account with which my web application runs doesn't have access to the certificate. So I had to add the user account  "Network Service" full rights to the certificate. It is done as below:

1. Type mmc(Microsoft Management Console) from run.
2. Click on File --> Add/Remove Snaps-ins
3. Select Certificates from Available snap-ins and Add to Selected snap-ins.
4. Use Computer account from next prompt.
5. Click Next and select Local Computer and click Finish, now the certificate is added in selected snap-ins list, click OK.
6. Click certificates and find your certificate and right click --> All tasks --> Manage Private Keys
7. Add "NETWORK SERVICE" account with Full control. Click OK.

After this you will be able to access the third party web service from your web app hosted in IIS.

Basically the account with which your web app runs needs to have access to the certificate. This worked for me like a charm.

Hope this helps somebody without wasting much time trying to figure out, where does the error come from.

WCF-CommunicationException-This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details.

I got this error when trying to send a list of 5000 items (large list). Setting maxItemsInObjectGraph="2147483647" didn't help.

An error occurred while receiving the HTTP response to http://localhost:51923/Service1.svc. This could be due to the service endpoint binding not using the HTTP protocol. This could also be due to an HTTP request context being aborted by the server (possibly due to the service shutting down). See server logs for more details.

SOLUTION

=========

Added <httpRuntime maxRequestLength ="262144" executionTimeout="103600"/> in the <system.web> section. It worked.

Hope this helps you.

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..).

Reuse a StringBuilder object after clearing the TEXT - Simplest way - in older .net frameworks..

StringBuilder doesn't have a Clear() to clear the text for .net framework 2.0. There are different ways to clear the text, which are following:

1. Use Remove() method and give full length of text to remove.
2. Use Replace() method and replace the characters with "".

3. And the EASIEST SOLUTION is just set the builder.length = 0;

check it out.

Operation is not valid due to the current state of the object - LinQ to Entities - table without a primary key

You get this error when trying to insert into a table without a primary key. If you open the edmx file in XML view and take a look into the EntitySet it will be having a DefiningQuery tag (see below).

<EntitySet Name="Table1" EntityType="Model2.Store.Table1" store:Type="Tables" store:Schema="TABDATA" store:Name="Table1">

<DefiningQuery>

 SELECT "Table1"."Column1" as "Column1",

"Table1"."Column2" as "Column2",

"Table1"."Column3" as "Column3",

"Table1"."Column4" as "Column4",

"Table1"."Column5" as "Column5",

"Table1"."Column6" as "Column6",

"Table1"."Column7" as "Column7"

from "TABDATA"."Table1" "Table1"

</DefiningQuery>
</EntitySet>

The solution is to remove the DefiningQuery tag for that EntitySet as below:

<EntitySet Name="Table1" EntityType="Model2.Store.Table1" store:Type="Tables" store:Schema="TABDATA" store:Name="Table1">

</EntitySet>

You can insert new records now without any error.

How to Put a website into maintanence/redirect to maintanence page (for changes/updates) in shortest way possible - maintanence page display

Create a file named app_offline.htm. This file can have the content you want to show when your site goes into maintanence mode. You can add nice images and good content to show it to users, whenever your site goes down for maintanence.

You can bring up the site after updates/maintanence by just renaming this file (app_offline.htm) to some other name. Yes, the name is really checked by ASP.net, which internally brings the site down.

Try it out....

Interview Question for you to think - Puzzle - HOW DO YOU DO THAT???

You have a 5 litres and a 3 litres cup. Make use of this 5 litres and 3 litres cup to exactly get 4 litres of water. Yes, I really mean exactly 4 litres of water..... HOW DO YOU DO THAT????

You have lots of water available and throw away any amount of water, it doesn't matter. Remember, you have only these two cups available. No other cups/measuring items available..

Think and post you answer.....

Deprecated Method for adding a new object to the [object] EntitySet. Consider using the .Add method of the associated ObjectSet property instead.

This is a message shown when using old method to Add objects to entity.

The old way (deprecated) of inserting a new record to database using Entity object was as below:
using (Entities entities = new Entities(entityConnStr))
{
   //....
  //.....
  entities.AddToMyTable1(myTableObject);
  entities.SaveChanges(); 
}

New way to Insert/Add records is as below:
using (Entities entities = new Entities(entityConnStr))
{
//....
//.....
entities.AddObject("MyTable1",myTableObject);
entities.SaveChanges();
}