In this article, I will explain you how to parse XML or import XML to SQL Server Database Table.

Here are the XML File:

<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
  <Address Type="Shipping">
    <Name>Ellen Adams</Name>
    <Street>123 Maple Street</Street>
    <City>Mill Valley</City>
    <State>CA</State>
    <Zip>10999</Zip>
    <Country>USA</Country>
  </Address>
  <Address Type="Billing">
    <Name>Tai Yee</Name>
    <Street>8 Oak Avenue</Street>
    <City>Old Town</City>
    <State>PA</State>
    <Zip>95819</Zip>
    <Country>USA</Country>
  </Address>
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
  <Items>
    <Item PartNumber="872-AA">
      <ProductName>Lawnmower</ProductName>
      <Quantity>1</Quantity>
      <USPrice>148.95</USPrice>
      <Comment>Confirm this is electric</Comment>
    </Item>
    <Item PartNumber="926-AA">
      <ProductName>Baby Monitor</ProductName>
      <Quantity>2</Quantity>
      <USPrice>39.98</USPrice>
      <ShipDate>2013-05-21</ShipDate>
    </Item>
  </Items>
</Order>
</Orders>

To Query XML File, you need to store in to xml variable @MyXML:

DECLARE @MyXML XML
SET @MyXML = '<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
  <Address Type="Shipping">
    <Name>....</Name>
    <Street>123 Maple Street</Street>
    <City>Mill Valley</City>
    <State>CA</State>
    <Zip>10999</Zip>
    <Country>...</Country>
  </Address>
  <Address Type="Billing">
    <Name>Tai Yee</Name>
    <Street>8 Oak Avenue</Street>
    <City>Old Town</City>
    <State>PA</State>
    <Zip>95819</Zip>
    <Country>...</Country>
  </Address>
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
  <Items>
    <Item PartNumber="872-AA">
      <ProductName>Lawnmower</ProductName>
      <Quantity>1</Quantity>
      <USPrice>148.95</USPrice>
      <Comment>Confirm this is electric</Comment>
    </Item>
    <Item PartNumber="926-AA">
      <ProductName>Baby Monitor</ProductName>
      <Quantity>2</Quantity>
      <USPrice>39.98</USPrice>
      <ShipDate>2013-05-21</ShipDate>
    </Item>
  </Items>
</Order>
</Orders>

Now, you use SQL query to get Order header from @MyXML:

SELECT Col.value('@OrderNumber', 'int') AS 'Order',
Col.value('@OrderDate', 'date') AS 'OrderDate',
Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',
Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',
Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',
Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',
Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',
Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',
Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',
Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',
Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',
Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',
Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',
Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country',
Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'
FROM @MyXML.nodes('/Orders/Order') AS T(Col)

Then, you can use SQL query to get Order Items from @MyXML:

SELECT
Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',
Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber' ,
Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,
Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',
Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',
Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',
Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate'
FROM @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)

To make it easier, here's the full code:

DECLARE @MyXML XML
SET @MyXML = '<?xml version="1.0"?>
<Orders>
<Order OrderNumber="99503" OrderDate="2013-10-20">
  <Address Type="Shipping">
    <Name>...</Name>
    <Street>123 Maple Street</Street>
    <City>Mill Valley</City>
    <State>CA</State>
    <Zip>10999</Zip>
    <Country>...</Country>
  </Address>
  <Address Type="Billing">
    <Name>Tai Yee</Name>
    <Street>8 Oak Avenue</Street>
    <City>Old Town</City>
    <State>PA</State>
    <Zip>95819</Zip>
    <Country>...</Country>
  </Address>
  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
  <Items>
    <Item PartNumber="872-AA">
      <ProductName>Lawnmower</ProductName>
      <Quantity>1</Quantity>
      <USPrice>148.95</USPrice>
      <Comment>Confirm this is electric</Comment>
    </Item>
    <Item PartNumber="926-AA">
      <ProductName>Baby Monitor</ProductName>
      <Quantity>2</Quantity>
      <USPrice>39.98</USPrice>
      <ShipDate>2013-05-21</ShipDate>
    </Item>
  </Items>
</Order>
</Orders>'
   SELECT
    Col.value('@OrderNumber', 'int') AS 'Order',
    Col.value('@OrderDate', 'date') AS 'OrderDate',
    Col.value('(Address[@Type=''Shipping'']/Name/text())[1]', 'nvarchar(50)') AS 'Shipping_Name',
    Col.value('(Address[@Type=''Shipping'']/Street/text())[1]', 'nvarchar(50)') AS 'Shipping_Street',
    Col.value('(Address[@Type=''Shipping'']/City/text())[1]', 'nvarchar(50)') AS 'Shipping_City',
    Col.value('(Address[@Type=''Shipping'']/State/text())[1]', 'nvarchar(50)') AS 'Shipping_State',
    Col.value('(Address[@Type=''Shipping'']/Zip/text())[1]', 'nvarchar(50)') AS 'Shipping_Zip',
    Col.value('(Address[@Type=''Shipping'']/Country/text())[1]', 'nvarchar(50)') AS 'Shipping_Country',
    Col.value('(Address[@Type=''Billing'']/Name/text())[1]', 'nvarchar(50)') AS 'Billing_Name',
    Col.value('(Address[@Type=''Billing'']/Street/text())[1]', 'nvarchar(50)') AS 'Billing_Street',
    Col.value('(Address[@Type=''Billing'']/City/text())[1]', 'nvarchar(50)') AS 'Billing_City',
    Col.value('(Address[@Type=''Billing'']/State/text())[1]', 'nvarchar(50)') AS 'Billing_State',
    Col.value('(Address[@Type=''Billing'']/Zip/text())[1]', 'nvarchar(50)') AS 'Billing_Zip',
    Col.value('(Address[@Type=''Billing'']/Country/text())[1]', 'nvarchar(50)') AS 'Billing_Country', 
    Col.value('(DeliveryNotes/text())[1]', 'nvarchar(250)') AS 'DeliveryNotes'
  FROM  @MyXML.nodes('/Orders/Order') AS T(Col)
  
   SELECT
    Col.value('(../../../Order/@OrderNumber)[1]', 'int') AS 'Order',
    Col.value('(@PartNumber)[1]', 'nvarchar(50)') AS 'PartNumber'  ,
    Col.value('(ProductName/text())[1]', 'nvarchar(250)') AS 'ProductName' ,
    Col.value('(Quantity/text())[1]', 'int') AS 'Quantity',
    Col.value('(USPrice/text())[1]', 'float') AS 'USPrice',
    Col.value('(Comment/text())[1]', 'nvarchar(250)') AS 'Comment',
    Col.value('(ShipDate/text())[1]', 'date') AS 'ShipDate' 
  FROM  @MyXML.nodes('/Orders/Order/Items/Item') AS T(Col)

Here is the Output:

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

In this article, you will play around with some SQL Script to identify blocking behaviour. The most basic script that I have been using and used by many DBA will include Activity Monitor, sp_who2, sysprocesses etc.

Recently, I saw a script written by one person:

SELECT * FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id IS NOT NULL
GO

This will shows the rows like:

This is so cool because I know who is waiting for whom. In the above image 53 is waiting for 68. While 79 is waiting for 53. This was a cool way to look at things. Sure, you wanted to show the same data in slightly different way using T-SQL. So, I wrote a Blocking Tree TSQL script:

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

The output would look like:

That's a simple way to look at the same Blocking data inside SSMS.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

SQL Server database files are organized in 8KB (8192 bytes) chunks, called pages. When we create the first row in a table, SQL Server allocates an 8KB page to store that row. Similarly every row in every table ends up being stored in a page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page. To do so, use the following undocumented T-SQL %%physloc%% virtual column:

USE AdventureWorks2014
GO
SELECT *, %%physloc%% AS physloc
FROM Person.AddressType
ORDER BY physloc;

As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:

SELECT *
FROM Person.AddressType
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

The sys.fun_PhysLocCracker function takes the %%physloc%% and represents a human readable format fileid, pageid i.e. 880 and record number on the page 880.

If you are interested in knowing what’s inside the sys.fn_PhysLocCracker function, use sp_helptext as follows:

EXEC sp_helptext 'sys.fn_PhysLocCracker'
which display the definition of sys.fn_PhysLocCracker
-------------------------------------------------------------------------------
-- Name: sys.fn_PhysLocCracker
--
-- Description:
--    Cracks the output of %%physloc%% virtual column
--
-- Notes:
-------------------------------------------------------------------------------
create function sys.fn_PhysLocCracker (@physical_locator binary (8))
returns @dumploc_table table
(
    [file_id]    int not null,
    [page_id]    int not null,
    [slot_id]    int not null
)
as
begin
    declare @page_id    binary (4)
    declare @file_id    binary (2)
    declare @slot_id    binary (2)
    -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
    --
    select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
    select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
    select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))
  
    insert into @dumploc_table values (@file_id, @page_id, @slot_id)
    return
end

The undocumented sys.fn_PhysLocCracker works on SQL Server 2008 and above.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Before reading this text there is only one prerequisite condition; you must know how to create a directive in AngularJs and why we need to create them. First of all you need to add AngularJs and jQuery to your page, similar to here:
<head runat="server"> 
<title></title> 
<script src="angular.min.js"></script> 
<script src="jquery-1.11.1.min.js"></script> 
</head>

You will either download them from my code that is available at the start of this article or you can download from their official websites. Now i'm simply adding a textbox wherever i would like to allow only numbers using an AngularJs directive.
<body> 
<form ng-app="app" id="form1" runat="server"> 
<div>
<h3> Demo to Allow Numbers Only </h3>
<hr /> 
Provide Your Mobile Number: <input type="text" 
name="MobileNumber" 
class="form-control" 
allow-only-numbers /> 
<hr /> 
</div> 
</form> 
</body>

Now it's time for Angular code. Write the following code:
<script> 
var app = angular.module('app', []); 
app.directive('allowOnlyNumbers', function () { 
return { 
restrict: 'A', 
link: function (scope, elm, attrs, ctrl) { 
elm.on('keydown', function (event) { 
if (event.which == 64 || event.which == 16) { 
// to allow numbers 
return false; 
} else if (event.which >= 48 && event.which <= 57) { 
// to allow numbers 
return true; 
} else if (event.which >= 96 && event.which <= 105) { 
// to allow numpad number 
return true; 
} else if ([8, 13, 27, 37, 38, 39, 40].indexOf(event.which) > -1) { 
// to allow backspace, enter, escape, arrows 
return true; 
} else { 
event.preventDefault(); 
// to stop others 
return false; 
} 
}); 
} 
} 
}); 
</script>

Here I first created a module named "app". The module creation is a necessary part if you wish to use Angular. This module is added to the HTML page using the ng-app directive. Then I created the directive with the name "allowOnlyNumbers". If you're making any kind of directive using AngularJs then you need to continually check that the name is provided using "-" in your HTML page and these dashes "-" are replaced by capital letters in the Angular code. Since I had applied this directive to the attribute of the textbox I had restricted this directive using "A" for Attribute.

I had applied this directive on the keydown event of the textbox. After those varied conditions are applied to the ASCII values of the keys to permit or to prevent them. In any case our ASCII values aren't allowed. event.preventDefault() will take away that character and can revert to the previous value. But still I had the intense problem of all the characters that were entered by pressing the shift + key weren't removed by this code, therefore I used easy jQuery at the beginning of the code to help prevent this situation.
var $input = $(this); 
var value = $input.val(); 
value = value.replace(/[^0-9]/g, '') 
$input.val(value); 

Then, write the following code:
app.directive('allowOnlyNumbers', function () { 
return { 
restrict: 'A', 
link: function (scope, elm, attrs, ctrl) { 
elm.on('keydown', function (event) { 
var $input = $(this); 
var value = $input.val(); 
value = value.replace(/[^0-9]/g, '') 
$input.val(value); 
if (event.which == 64 || event.which == 16) { 
    // to allow numbers 
    return false; 
} else if (event.which >= 48 && event.which <= 57) { 
    // to allow numbers 
    return true; 
} else if (event.which >= 96 && event.which <= 105) { 
    // to allow numpad number 
    return true; 
} else if ([8, 13, 27, 37, 38, 39, 40].indexOf(event.which) > -1) { 
    // to allow backspace, enter, escape, arrows 
    return true; 
} else { 
    event.preventDefault(); 
    // to stop others 
    //alert("Sorry Only Numbers Allowed"); 
    return false; 
} 
}); 
} 
} 
}); 

Output:
Now our application is made and we will check the output. On running the application an easy textbox are going to be shown wherever only numbers are allowed.

If you press the numbers from anywhere within the key, they will be allowed however all the other characters won't be allowed to exist, although they're special characters.

HostForLIFE.eu AngularJS Hosting

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.