To support multi-value params, many times we use XML data type as input param.

There are multiple ways to parse XML data in SQL Server. Lets have a look at them

We will use the following XML data

<ShoppingCart>
<Purchase ProductID="7" Price="10.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="99" Price="25.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="32" Price="12.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="11" Price="90.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="7" Price="50.00" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="8" Price="67.35" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="45" Price="29.99" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
<Purchase ProductID="54" Price="49.49" SaleDate="10/11/2006" SaleBatchID = "4523"
CustomerID = "2398"/>
</ShoppingCart>

OPENXML
To parse the XML using OPENXML we use the following code

In this script post, we will look at different ways to get the list of user defined tables in a particular database, along with their rowcount and size information.

Method 1: Using count(*)

In this method we will get the rowcount for each table using the sp_msforeachtable in combination with count(*) function. Note using this method we will only get the rowcount for each table.

IF OBJECT_ID('tempdb..#tableRowCount') IS NOT NULL
DROP TABLE #tableRowCount
CREATE TABLE #tableRowCount
(
name       VARCHAR(512),
ROWS       INT
)
insert into #tableRowCount
exec sp_msforeachtable 'select ''?'' ,COUNT(*) FROM ?'
select * from #tableRowCount

Method 2: Using sp_spaceused

Here, we will get the rowcount for each table using the sp_msforeachtable in combination with sp_spaceused stored procedure. Using this method we will get both the rowcount as well as spaceused in KB for all the tables.

In this script we will look at various ways we can design a string split function in SQL Server to split a delimited string in to individual rows.

We will use the following csv delimited string for building our split function.

DECLARE @csv_str VARCHAR(8000)
SET @csv_str = 'cat,dog,rat,ant,tiger'

Method 1 : USING charindex function

In this method we will use the system string function charindex. We loop through the string looking for the delimiter and parse the string accordingly.

What is a Java Drive-by?

“Download of spyware, a computer virus or any kind of malware that happens without knowledge of the user. Drive-by downloads may happen by visiting a website, viewing an e-mail message or by clicking on a deceptive popup window: the user clicks on the window in the mistaken belief that, for instance, it is an error report from his own PC or that it is an innocuous advertisement popup; in such cases, the “supplier” may claim that the user “consented” to the download though he was completely unaware of having initiated a malicious software download”

Watch out for CryptoPHP, a threat that uses backdoored

Joomla, WordPress and Drupal themes and plug-ins to

compromise webservers.

Just came across an incidence of a malicious executable social.png that got loaded with a plugin that created a lethal infection of a Word Press Website. Lesson learned from it is to NEVER use pirated plugins. It is not worth it. Also, it may have ramifications for webservers.

The person who went through this experience is above average WordPress literate, is careful with plugins, and has WordFence loaded on her WordPress site. Wordfence found the social.png, but by the time she learned about it from Wordfence the Data Center that was hosting her account had picked up on the “CryptoPHP” exploit, and the account was immediately suspended without an option to make a backup. Looks as though it is so lethal Wordfence could not stop it. The social.png replicates itself and is very difficult to root out.