How to OPEN & READ xml document in SQL 2008 R2:

In this post we are going to look into open and read an XMLdocument in SQL. In sql, we have some predefined stored procedures which hepls to prepare and remove XML files those are:

sp_xml_preparedocument,
sp_xml_removedocument

And OPENXML command executes XML handler to read and display the content. Find the below script to read and insert the XML data into a table.

CREATE  PROCEDURE [dbo].[xmlProducts]   (  @XmlOverView  NVARCHAR(MAX)  = null  )  AS    BEGIN      /***********************************************************************************    ** Desc                                      : SP to save Products details with XML documents ** Author          : Ramakrishna K ** Date                                      : 06/08/2012 ***********************************************************************************/         /***************************************    * Standard SET statements.    ***************************************/    SET NOCOUNT ON    SET ANSI_PADDING ON    SET ANSI_NULLS ON    SET ANSI_WARNINGS ON    SET CONCAT_NULL_YIELDS_NULL ON    SET QUOTED_IDENTIFIER OFF           /***************************************    * Genric variables declaration ***************************************/    DECLARE     @Msg  VARCHAR(1000),   -- To display message text     @Error  INT           -- Captuer errors                SELECT     @Error = 0,      @Msg = ''      /***************************************  *    SP declared variables  ***************************************/  DECLARE  @TransactionExists BIT,  @PID   INT, @PName NVARCHAR(100), @iDoc    INT                         BEGIN TRY                                                                                   --The number of BEGIN TRANSACTION statements that have occurred on the current connection.                                                  IF @@TRANCOUNT > 0                                                                            SET @TransactionExists = 1                                                     ELSE                                                                            SET @TransactionExists = 0                                                                                  IF @TransactionExists = 0                                                     BEGIN                                                                              --Initialise transaction                                                                                 BEGIN TRANSACTION                                                     END                                                                               ---------------Section to save the product details into a table--------------------------                                                                             IF(@XmlOverView != NULL OR @XmlOverView != '')                                                  BEGIN                                                                                EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @XmlOverView                                                                                                                          INSERT INTO Production.xmlTest                                                                                                                                                 (PID                                                                                                                                                                         ,PName                                                                                                                         )                                                                                                                                                                                                                                                   SELECT * FROM OPENXML(@iDoc, '/NewDataSet/Table',2)                                                                                                                                                                          WITH (ProductID  INT                                                                                                                                                                                                                          ,Name  NVARCHAR(500)                                                                                                                                                                          )                                                                                                                             EXECUTE sp_xml_removedocument @iDoc                                                                             IF @TransactionExists = 0                                                                           BEGIN                                                                                                     COMMIT TRANSACTION                                                                                                     RETURN (0)                                                                           END                                                 END                         END TRY                           BEGIN CATCH                                                 IF @TransactionExists = 0                               ROLLBACK TRANSACTION                                                     SET @msg = Error_Message()                            RAISERROR (@msg,16,1) WITH NOWAIT                                                         IF @Error = 0                               SET @Error = 1                              RETURN (@Error)                         END CATCH     END

T-SQL Programming (SQL Server 2008 R2)

Programming with T-SQL

• Stored Procedure
• A stored procedure is a pre-compiled collection of transact-sql statements
• A stored procedure encapsulates repetitive tasks
• A stored procedure can accept input parameters and returns status value to indicate success or failure
• A stored procedure can return multiple output parameters.
• T-SQL statements that make up the body of a stored procedure can perform database operations like INSERT, DELETE, UPDATE, and SELECT as well as call another stored procedure(s).
• To view the definition of stored procedure sp_helptext
• Drop a single stored procedure DROP PROCEDURE procedure _name
• Dropping multiple stored procedures DROP PROCEDURE procedure1, procedure2
• Advantages:
• Share application logic
• Shield database schema details
• Provide security mechanisms
• Improve performance
• Stored procedures implement many tasks as a series of T-SQL statements. Conditional logic can be applied to the results of the first T-SQL statements to determine which subsequent Transcat-SQL statements are executed.
• Reduce network traffic
• Rather than sending hundreds of Transact-SQL statements over the network, users can perform a complex operation by sending a single statement, which reduces the number of requests that pass between client and server.
• The maximum size of a stored procedure is 128MB depending on available memory.
• User Defined Functions
• Working with Cursors
• Cursors can be consider as named results sets which allow a user to move through each record one by one
• Triggers
• A trigger is a special type of stored procedure
• A trigger is:
• Associated with a table
• Invoked automatically
• Not called directly
• Treated as part of the transaction that fired it

TSQL - Execution

Batch is one or more SQL server commands that are dispatched and executed together.

Command Line Execution Steps;

Step1: Parase commands and create the sequence tree

The command parser checks for proper syntax and translates the Transact-SQL commands into an internal format that can be operated on. The internal format is known as a sequence tree or query tree. The command parser handles these language events.

Step2: Compile the batch

An execution plan is generated from the sequence tree. The entire batch is compiled, queries are optimized, and security is checked. The execution plan contains the necessary steps to check and constrains that exist. If an after trigger exists for any data modification statement, the call to that trigger is appended to the execution plan for the statement.

Step3: Execute

During execution, each step of the execution plan is dispatched serially to a manager that's responsible for carrying out that type of command. A statement with an explicit BEGIN TRAN interacts directly with the transaction manager.

Step4: Recompile Execution Plans

It should be clear that the SQL code for routines persists in the database but execution plans don't. Execution plans are cached memory. But sometimes they can be invalidated and a new generated.

We can use GO or EXEC command to execute the batch files

Types of TSQL Statements:

Data Query Langauge (DQL)

• Commands used to get data from the database and impose ordering upon it

Data Manipulation Language (DML)

• Users move data into out of databases and also modify the data in the database
• SELECT, INSERT, UPDATE, DELETE

Data Definition Language (DDL)

• Is used to create, alter, and delete database objects
• CREATE, ALTER, DROP, TRUNCATE

Data Control Language (DCL)

• Consists of commands that control the user access to the database objects
• GRANT, DENY, REVOKE

Transaction Control Language (TCL)

• Commands allow the users to control transaction 
• ROLLBACK, COMMIT

SELECT statements with CASE expressions:

Normally when we are working with T-SQL queries we may have scenarios to use CASE expression to filter or filter the data based on conditionals. CASE expressions are very use full to acheive the above.

Eg: Example below gives you the breif idea how to use SELECT with CASE expressions.

IF EXISTS:

we may a face situation that need to verify the given 'value' or 'key' exists a table. SQL provides EXISTS key word in order to check the given value. In such scenarios, we can use the follwing IF condintion with EXISTS to procced the process furhter.

Eg: In a SELECT query if the value present in the table then return 1, so that condition will be true.

 

Table Variable:

Table variables are used within the scope of the routine or batch which they are defined. And its an alternative of Temporary table which can do all kinds of operations that we can perform in Temp table.

Example: Below is the syntax of table variable

USE AdventureWorks

GO

SET NOCOUNT ON

DECLARE @FirstTable TABLE (RandomInteger INT)

DECLARE @SecondTable TABLE (RandomInteger INT)

DECLARE @WhenWeStarted DATETIME

DECLARE @ii INT

BEGIN TRANSACTION

SET @ii = 0

WHILE @ii < 100

  BEGIN

    INSERT  INTO @FirstTable VALUES  (RAND() * 10000)

   

    SET @ii = @ii + 1

  END

--SELECT * FROM @FirstTable

 

SET @ii = 0

WHILE @ii < 100

  BEGIN

    INSERT  INTO @SecondTable VALUES  (RAND() * 10000)

   

    SET @ii = @ii + 1

  END

COMMIT TRANSACTION

SELECT  @WhenWeStarted = GETDATE()

SET STATISTICS PROFILE ON

SELECT  COUNT(*)

FROM    @FirstTable first

        INNER JOIN @SecondTable second

        ON first.RandomInteger = second.RandomInteger OPTION (RECOMPILE)

NOTE: We can use table variable when we are dealing with small amount of data or as function parameter in SPs