European Windows 2012 Hosting BLOG

BLOG about Windows 2012 Hosting and SQL 2012 Hosting - Dedicated to European Windows Hosting Customer

SQL Server 2016 Hosting - HostForLIFE.eu :: How to Parse & Import XML into Database Table

clock August 27, 2015 09:03 by author Rebecca

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:

XML File:

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 2016 Hosting - HostForLIFE.eu :: How to Use SQL Script to Identify Blocking Chain

clock August 20, 2015 07:16 by author Rebecca

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.

 



HostForLIFE.eu Launches Umbraco 7.2.8 Hosting

clock August 19, 2015 06:57 by author Peter

HostForLIFE.eu, a leading web hosting provider, has leveraged its gold partner status with Microsoft to launch its latest Umbraco 7.2.8 Hosting support

HostForLIFE.eu, a leading Windows web hosting provider with innovative technology solutions and a dedicated professional services team, today announced the support for Umbraco 7.2.8 hosting plan due to high demand of Umbraco users in Europe. The company has managed to build a strong client base in a very short period of time. It is known for offering ultra-fast, fully-managed and secured services in the competitive market.

HostForLIFE.eu hosts its servers in top class data centers that is located in Amsterdam (NL), London (UK), Paris (FR), Frankfurt (DE) and Seattle (US) to guarantee 99.9% network uptime. All data center feature redundancies in network connectivity, power, HVAC, security and fire suppression. All hosting plans from HostForLIFE.eu include 24×7 support and 30 days money back guarantee. HostForLIFE Umbraco hosting plan starts from just as low as €3.00/month only and this plan has supported ASP.NET 4.5, ASP.NET MVC 5/6 and SQL Server 2012/2014.

Umbraco 7.2.8 is a fully-featured open source content management system with the flexibility to run anything from small campaign or brochure sites right through to complex applications for Fortune 500's and some of the largest media sites in the world. Umbraco was sometimes unable to read the umbraco.config file, making Umbraco think it had no content and showing a blank page instead (issue U4-6802), this is the main issue fixed in this release. This affects people on 7.2.5 and 7.2.6 only. 7.2.8 also fixes a conflict with Courier and some other packages.

Umbraco 7.2.8 Hosting is strongly supported by both an active and welcoming community of users around the world, and backed up by a rock-solid commercial organization providing professional support and tools. Umbraco 7.2.8 can be used in its free, open-source format with the additional option of professional tools and support if required. Not only can you publish great multilingual websites using Umbraco 7.2.8 out of the box, you can also build in your chosen language with our multilingual back office tools.

Further information and the full range of features Umbraco 7.2.8 Hosting can be viewed here: http://hostforlife.eu/European-Umbraco-728-Hosting

About HostForLIFE.eu

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

HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.asp.net/hosting/hostingprovider/details/953). Their service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, they have also won several awards from reputable organizations in the hosting industry and the detail can be found on their official website.



SQL Server 2016 Hosting - HostForLIFE.eu :: How to Find Corrupt Records of The Pages

clock August 10, 2015 06:00 by author Rebecca

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.

 



AngularJS Hosting - HostForLIFE.eu :: Learn How to Make a Directive to Allow only Numbers using AngularJs

clock August 5, 2015 08:36 by author Peter

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.



About HostForLIFE.eu

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.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in