SQL Server : Sample for reading xml data

Goal

Simple SQL query to fetch data from an xml.

--//---------- Declare ----------//
DECLARE @xmlSample XML;

SET @xmlSample = '<GUIDs>
  <GUID Temp="a">1</GUID>
  <GUID Temp="a">2</GUID>
  <GUID Temp="a">3</GUID>
  <GUID Temp="b">4</GUID>
  <GUID Temp="b">5</GUID>
</GUIDs>';

--//---------- Set Query ----------//
SELECT * FROM 
(
    SELECT 
        d.value('.', 'INT') AS GUID , 
        d.value('@Temp', 'VARCHAR(36)') AS Temp
    FROM @xmlSample.nodes('/GUIDs/GUID') d(d) 
) AS A

Change the path of TempDB in SQL Server 2005

Error : System databases master, model, msdb, and tempdb cannot be detached.

use master

go

Alter database tempdb modify file (name = tempdev, filename = 'D:\SQL Database 2005\tempdb.mdf')

go

Alter database tempdb modify file (name = templog, filename = 'D:\SQL Database 2005\templog.ldf')

go

Read XML in SQL Server 2005/2008

Can read an xml file format in SQL Server 2005 2005/2008

To read the attributes use "@AttributeName"

To read the node under it use "NodeName[0]"

Just copy/patse the below given stuff in sql server and run it.

--//---------- Declare ----------//

DECLARE @xmlTest XML    

SET @xmlTest = '<GUIDs>

  <GUID Temp="a">1</GUID>

  <GUID Temp="a">2</GUID>

  <GUID Temp="a">3</GUID>

  <GUID Temp="b">4</GUID>

  <GUID Temp="b">5</GUID>

</GUIDs>'



--//---------- Set Query ----------//

SELECT Temp , MAX(GUID) FROM (

SELECT xmlTest.value('.', 'INT') AS GUID , xmlTest.value('@Temp', 'VARCHAR(36)') AS Temp

FROM @xmlTest.nodes('/GUIDs/GUID') d(xmlTest) ) AS A

GROUP BY Temp

SQL 2005/2008 Query to retrieve a comma seperated single row from a table.

This query will help to combine all records in a single row result with comma seperation. I have taken both type string as well as integers.

-- Take a table variable

DECLARE @TBL as TABLE(VAL1 INT, VAL2 VARCHAR(10));

-- POPULATE THE VALUES

INSERT INTO @TBL VALUES(10,'A');

INSERT INTO @TBL VALUES(20,'B');

INSERT INTO @TBL VALUES(30,'C');

INSERT INTO @TBL VALUES(40,'D');

INSERT INTO @TBL VALUES(50,'E');

-- Show the values in tables

SELECT * FROM @TBL;



-- Declare variables

DECLARE @CommaValsInt AS VARCHAR(MAX);

DECLARE @CommaValsString AS VARCHAR(MAX);



-- Gets the sql query with Comma Seperated

SELECT 

    @CommaValsInt = COALESCE(@CommaValsInt + ',', '') + CAST(VAL1 AS VARCHAR(10)) 

    , @CommaValsString = COALESCE(@CommaValsString + ''',''', '''') + VAL2

FROM 

    @TBL;

-- Show the result

SELECT @CommaValsInt AS ResultForInt , (@CommaValsString + '''') ResultForString

Calculate Standard Deviation Using SQL Server

SQL Procedure to get the Standard Deviation

Hello everyone, here is the formaula to calculate the standard deviation.

---

-- Create a temp table insert some rows as raw data.

DECLARE @tbl AS TABLE(ID INT IDENTITY(1,1) , Val DECIMAL(18,4));

INSERT INTO @tbl VALUES (10.20 );

INSERT INTO @tbl VALUES (99.27 );

INSERT INTO @tbl VALUES (23.56 );

INSERT INTO @tbl VALUES (56.92 );

INSERT INTO @tbl VALUES (02.37 );

SELECT * FROM @tbl;



-- Declare variables @AVG , @Count.

-- @AVG is the average of the "Val".

-- @Count is the number of records in the table.

DECLARE @AVG DECIMAL(18,4), @Count INT;

SELECT @AVG = AVG(VAL) , @Count = COUNT(ID) FROM @tbl;

SELECT @AVG AS Average , @Count AS RecordCount;



-- Declare the sum of Deviation

-- FORMULA = Single value is substracted by the average. The square of the result and sum of all the values will get "@SumDev".

DECLARE @SumDev DECIMAL(18,4);

SELECT @SumDev = SUM( SQUARE(VAL - @AVG) ) AS SumSquareDeviation FROM @tbl;

SELECT @SumDev;



-- Get the final stanard Deviation

-- FORMULA = Gets the last @SumDev divide it by a number less than the total number of rows(@Count - 1). Get the quareroot and get the Standard Deviation

SELECT SQRT((@SumDev /(@Count-1))) AS StandardDeviation;

---