Using functions of xml datatype in SQL Server 2005

Click here

Xml Best Practices

MSDN link

Query to select records from Xml

I have following Xml in Job table which column name is JobXml (Xml datatype)

<Job>
<TimeID Value="2323" id="a"/>
<TimeID Value="4445" id="b"/>
<TimeID Value="3453" id="a"/>
<TimeID Value="7677" id="c"/>
</Job>

I want to write a select query who returns all TimeID Value where id= "a" like this

2323
3453

Following is the query for that:

SELECT r.value('@Value','int')
FROM Job
CROSS APPLY StatisticsXml.nodes('/Job/TimeID[@id=''a'']') AS x(r)

Convert ArrayList into Array

ArrayList arrayList = new ArrayList();
arrayList.Add("paresh");
arrayList.Add("sonal");
string[] stringArray = (string[])arrayList.ToArray(typeof(string));

Read Xml Value in SQL Server 2005

Hi,

Here I am giving the example how to read XML fields in SQL Server query.

Database Table jobtable contains jobxml field which datatype is xml
You can query to read xml. It is very simple to use.


Suppose you have following XML :

<scheduler_job>
<submitter>
<email notify="false" id="5">pdehadray</email>
</submitter>
</scheduler_job>


Query to read email node inner text is as follows:
select jobxml.value('(/scheduler_job/submitter/email)[1]','varchar(50)') from jobtable

Query to read notify attribute value in email node is as follows:
select jobxml.value('(/scheduler_job/submitter/email/@notify)[1]','varchar(50)') from jobtable

Query to read id attribute value in email node is as follows:
select jobxml.value('(/scheduler_job/submitter/email/@id)[1]','int') from jobtable



If XML contains two node with same name like this:

<scheduler_job>
<submitter>
<email notify="false" id="5">pdehadray</email>
<email notify="true" id="6">rjaiswal</email>
</submitter>
</scheduler_job>


Query to read 1st email node inner text is as follows:
select jobxml.value('(/scheduler_job/submitter/email)[1]','varchar(50)') from jobtable

Query to read 2nd email node inner text is as follows:
select jobxml.value('(/scheduler_job/submitter/email)[2]','varchar(50)') from jobtable



Query to read notify attribute value in 1st email node is as follows:
select jobxml.value('(/scheduler_job/submitter/email/@notify)[1]','varchar(50)') from jobtable

Query to read notify attribute value in 2nd email node is as follows:
select jobxml.value('(/scheduler_job/submitter/email/@notify)[2]','varchar(50)') from jobtable



Query to read id attribute value in 1st email node is as follows:
select jobxml.value('(/scheduler_job/submitter/email/@id)[1]','int') from jobtable

Query to read id attribute value in 2nd email node is as follows:
select jobxml.value('(/scheduler_job/submitter/email/@id)[2]','int') from jobtable

Hope it will be useful for you.

Get comma (or any character) delimited string from String Array

If you have stringArray as string array and you want a comma delimited string then don’t apply any self logic. Optimize function is given in .Net. Following is the code snippet in C#.Net

string delimitedString = string.Join(",", stringArray);

Case Sensitive Search in MS SQL Server 2005

If you want Case Sensitive search in SQL Server 2005 Query then try this:


select * from qpsjobqueue where jobname COLLATE Latin1_General_CS_AS like 'First%'

or

select * from qpsjobqueue where jobname COLLATE Latin1_General_CS_AS = 'FirstTest'