XSD in SQL Server

Recently, I have started reading Jacob's (Fellow SQL XML MVP) book and learnt lot of things on XSD and XML schema.

I wish to share the same to my blog readers with my short articles. Let us enter into the topic,


XSD:
The abbreviation for XSD is XML Schema definition. This will define, how a XML should look like.
If we think about the XML (its worth to write about XML data type in SQL server. Let me do it in my next article) will be of two types,



1. Typed XML (XML bounded to XSD definition)
2. Untyped XML (XML is not bounded to any XSD definition).



As of now, We need to concentrate on typed XML. In this case,


1. A schema will be created - It will explain, how a XML should look like.
2. Create a XML which will abide the schema created already.



On looking into the structure of the shema,
1. It should be started with the XML Namespace.
2. Followed by the root elements.
3. The root element may have any number of child element.



Let's see a sample XSD,

The XMLName space should be inherited from the namespace
http://www.w3.org/2001/XMLSchema

Here is the code to create XSD,

We can see how XML is created based on this shema in my future articles.

Thanks and Regards,

Venkatesan Prabu .J

Records in the first table based on the second table reference

Hi All,

Today, am trying to fetch a record from the first table and replace the values with the reference records in the second table. Let's make a try. There will be lot of ways to do this, am taking a legacy method to arrive the same. Let's see,

create table venkat(id int, id1 int, nam varchar(100))
insert into venkat values(1,2,'arun')
insert into venkat values(2,null,'arun1')
insert into venkat values(1,3,'arun2')
select * from venkat
create table venkat1(id int, val varchar(100))
insert into venkat1 values(1,'A')
insert into venkat1 values(2,'B')
insert into venkat1 values(3,'C')
select * from venkat1







select t1.val as id,t.val as id1,t.nam from
(
select b.val,a.nam from venkat a left outer join venkat1 b
on a.id1=b.id
)t inner join
(
select b.val,a.nam from venkat a left outer join venkat1 b
on a.id=b.id
)t1
on t.nam=t1.nam





Happy Learning!!!

Thanks and Regards,
Venkatesan Prabu .J

User defined function error in SQL Server

Today while trying to create a function, am trying to execute the function created. Am getting an error showing "'Venkat_fun2' is not a recognized built-in function name."

Strange behavior of SQL Server. I need to raise this issue with Microsoft techies. Let me check it and blog the same with the reason.

create function Venkat_fun2(@a int)
returns int
as begin
RETURN
(select id from venkat where id=@a)
end
select Venkat_fun2(1)

Thanks and Regards,

Venkatesan Prabu .J

Interesting behaviour of SQL Server

Estimation plan in SQL Server :
I happened to do a check on the estimation plan for a very small query

Executed both the query,
Query 1:
select * from [venkat] order by 1 desc -- Query cost is 82%
This query have scanned a little bit on the table and took all the remaining cost for sorting.
Query 2:
select * from [venkat]order by -- Query cost is 18%
(select 1)
desc
The above query has took 100% of its cost to scan the table. It doesn't spend any thing for sorting. Am confused with this behavior of sql server. But too interesting. I think we need to use second query for best performance (Providing, we need to index the table properly :-) )

Let me check with Microsoft team for a very better explanation on this article.

Thanks and Regards,

Venkatesan Prabu .J

Handling single quotes in SQL Server

In SQL Server, sometimes we will have single quotes in the values and sometimes we need to fetch the values from the tables. Seems to be interesting rights... Let see how can we achieve it,

Considering for example, I want to fetch the name Michael's. In that case, we can write

select * from table where name='michael's' -- More meaning ful

But, SQL Server won't recognise the above query and it will throw an error showing incorrect syntax because, SQL Server will recognise upto the letter "L" after that it won't recognise as a proper SQL Sentence.
select * from table where name='michael'

To over come this problem, SQL Server is providing a handy approach by putting two consecutive single quotes instead of one single quote. So the query should be,

select * from table where name='michael''s' --Query won't show any error and it will server the purpose.

Let see a small sample on this,


create table venkat(id int ,name varchar(100))
insert into venkat values(1,'venkat')
insert into venkat values(2,'venkat1')
insert into venkat values(3,'venkat2')
insert into venkat values(4,'venkat3')
insert into venkat values(5,'venkat4')
insert into venkat values(6,'venkat5')
insert into venkat values(6,'venkat''5')
select * from venkat

create procedure Venkat_sample
(@nam varchar(100))
as
begin
select * from venkat where name=@nam
end
exec venkat_sample 'venkat''5'



Thanks and Regards,
Venkatesan Prabu .J