FOR XML clause (SQL Server)

SQL Server > XML Data > FOR XML

You can retrieve results of a SQL query as XML by specifying the FOR XML clause in the query.

Examples

• Concatenate rows into lines

Example

create table customer

(
       id                  int,
       name         nvarchar(max)
)
go
create table [order]
(
       id                  int,
       Date         date,
       amount       int,
       customer_id  int
)
set nocount on
go

insert into
       customer (id, name)
values
       (1,'Customer 1')
insert into
       customer (id, name)
values
       (2,'Customer 2')
insert into
        [order] (id, Date, amount, customer_id)
values
       (1,GETDATE(),100, 1)
insert into
       [order] (id, Date, amount, customer_id)
values
       (2,GETDATE(),100, 1)
insert into
       [order] (id, Date, amount, customer_id)
values
       (3,GETDATE(),200, 1)
insert into
       [order] (id, Date, amount, customer_id)
values
       (4,GETDATE(),300, 2)

--query returns results as a rowset

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount    
from
       customer
       join [order] ord on customer.id = ord.customer_id

Result:

id            name                  order_id  Date                    amount

1             Customer 1        1               2013-03-04         100
1             Customer 1        2               2013-03-04         100
1             Customer 1        3               2013-03-04         200
2             Customer 2        4               2013-03-04         300
                                                   


--query returns results as XML






AUTO mode returns query results as nested XML elements

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount    
from
       customer
       join [order] ord on customer.id = ord.customer_id
FOR XML auto

Result:

<customer id="1" name="Customer 1">

  <ord order_id="1" Date="2013-03-04" amount="100" />
  <ord order_id="2" Date="2013-03-04" amount="100" />
  <ord order_id="3" Date="2013-03-04" amount="200" />
</customer>
<customer id="2" name="Customer 2">
  <ord order_id="4" Date="2013-03-04" amount="300" />
</customer>

RAW mode transforms each row in the query result set into an XML element that has the generic identifier , or the optionally provided element name.

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount    
from
       customer
       join [order] ord on customer.id = ord.customer_id
FOR XML raw

Result:

<row id="1" name="Customer 1" order_id="1" Date="2013-03-04" amount="100" />

<row id="1" name="Customer 1" order_id="2" Date="2013-03-04" amount="100" />
<row id="1" name="Customer 1" order_id="3" Date="2013-03-04" amount="200" />
<row id="2" name="Customer 2" order_id="4" Date="2013-03-04" amount="300" />

PATH mode provides a simpler way to mix elements and attributes.

select

       customer.id,
       customer.name,
       ord.id order_id,
       ord.Date,
       ord.amount    
from
       customer
       join [order] ord on customer.id = ord.customer_id
FOR XML path

 Result:

<row>
  <id>1</id>
  <name>Customer 1</name>
  <order_id>1</order_id>
  <Date>2013-03-04</Date>
  <amount>100</amount>
</row>
<row>
  <id>1</id>
  <name>Customer 1</name>
  <order_id>2</order_id>
  <Date>2013-03-04</Date>
  <amount>100</amount>
</row>
<row>
  <id>1</id>
  <name>Customer 1</name>
  <order_id>3</order_id>
  <Date>2013-03-04</Date>
  <amount>200</amount>
</row>
<row>
  <id>2</id>
  <name>Customer 2</name>
  <order_id>4</order_id>
  <Date>2013-03-04</Date>
  <amount>300</amount>
</row>

drop table [order]

drop table customer

Dynamic PIVOT and cross-tab query (SQL Server)

SQL Server > Operators > PIVOT > Dynamic PIVOT and cross-tab query




Sometimes we don't know values in FOR clause for pivot.
In this example we will build dynamic columns with values and use dynamic query to build pivot and cross-tab query.

Example:

create table pivot_data

(
       id           int,
       Date   date,
       amount int
)
set nocount on
go

insert into
       pivot_data (id, Date, amount)
values
       (1,GETDATE(),100)
insert into
       pivot_data (id, Date, amount)
values
       (1,dateadd(day,1, GETDATE()),200)
insert into
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,1, GETDATE()),300)
insert into
       pivot_data (id, Date, amount)
values
       (2,dateadd(month,2, GETDATE()),400)    

select * from pivot_data

Result:

id            Date                    amount

1             2013-03-04         100
1             2013-03-05         200
2             2013-04-04         300
2             2013-05-04         400

DECLARE

    @cols AS NVARCHAR(MAX),

    @query  AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT  ',' + QUOTENAME(c.Date )
            FROM pivot_data c
            order by Date
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')
print @cols

set @query = 'SELECT id, ' + @cols + ' from

            (
                select
                    id
                    , date
                    , amount
                from
                    pivot_data
           ) x
            pivot
            (
                sum(amount)
                for  date in (' + @cols + ')
            ) p '

execute(@query)

Result:

id 2013-03-04    2013-03-05        2013-04-04        2013-05-04

1 100                 200                     NULL                 NULL
2 NULL              NULL                 300                     400

drop table pivot_data

EXEC() error : could not find stored procedure (SQLServer)

Solution:

EXEC with brackets.

use master;

go

DECLARE

 @sql                nvarchar(max)
set   
  @sql = 'select * from sysdatabases'
-- EXEC without brackets () generate  error
exec   @sql

-- EXEC with brackets success
exec   (@sql )

Print SQL Server Example

SQL Server > Print

Print returns a user-defined message to the client.
The message string can be up to 8000 characters long

Example

declare @p int = 0

while @p < 10
begin
       print 'val ' + cast( @p as nvarchar(10))
    set @p = @p + 1
end

Result:

val 0
val 1
val 2
val 3
val 4
val 5
val 6
val 7
val 8
val 9

@@TRANCOUNT (SQL Server)

SQL Server > Built-In Functions > @@TRANCOUNT

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

Example:

PRINT @@TRANCOUNT

BEGIN TRAN --  The BEGIN TRAN statement will increment the  transaction count by 1.
PRINT @@TRANCOUNT
COMMIT     --  The COMMIT statement will decrement the  transaction count by 1.
PRINT @@TRANCOUNT

Result:
0
1
0

SQL query result insert in temp table

SQL Server > Create dynamic temp table

Select into create dynamical temporary table

Example

SELECT

   *
into
   #t
FROM
  table

Call REST (C#)

C# > WebRequestMethods.Http.Get

WebRequestMethods.Http.Get: Represents an HTTP GET protocol method.

The GET method retrieves the information or entity that is identified by the URI of the request.

Example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            string Uri = "Your Uri";
            String text;
            HttpWebRequest myReq = (HttpWebRequest)WebRequest.Create(Uri);
            myReq.Method = WebRequestMethods.Http.Get;
            myReq.Accept = "application/json";

            HttpWebResponse response = (HttpWebResponse)myReq.GetResponse();
            using (var sr = new StreamReader(response.GetResponseStream()))
            {
                text = sr.ReadToEnd();
            }
        }
    }
}