Thursday, April 24, 2014
Text Size

T-SQL: Parse an XML value

This took me a while to find so I've posted an article below detailing how to parse or extract values from a string containing XML code.

I'm working with a system which stores XML strings in a database and rather than a separate file, it stores these in a row.


Compare two databases using T-SQL

Applies to:
  • MS SQL Server Management Studio 2008
  • Two Transact-SQL databases
A quick article on how to compare two Microsoft databases using the tools provided with SQL Server and without having to download any third-party products.

I googled and binged and all I could find were people selling third party products... they don't get it. If you are reading this, it's likely you've paid for a commercial version of a Microsoft product which cannot possibly be cheap. MS SQL Server Management Studio (SSMS) comes included so use it...

Well I started with the method #1 listed here and then just built on this.

Split a row into multiple rows based on a column value

Applies to
  • Transact-SQL (T-SQL)
This is a quick article on how to split a single row into multipe rows based on the value of a column in the same table.

I have a table that has all the days of sickness of employees. This table contains, which employee, on what date, and for how many days they were off sick. When migrating to a new system, the destination wanted 1 row per day. This meant that if in the old system, there was 1 row with an employee who took 2 days off, we would want 2 rows for that date for the same employee in the new system.

What we have:

  1.  SELECT 
  2.          EmployeeNo 
  3.          , DaysOffSick 
  4.          , DateOfSickness 
  5.  FROM    Employees_Attendance_Table 
  8.  -- yields 
  10.          EmployeeNo      DaysOffSick     DateOfSickness 
  11.          --------------  --------------  ---------------- 
  12.          001             1.00            1997-11-17 
  13.          002             3.00            2000-02-18 
  14.          003             2.00            1999-02-25 
What we want:

  1.  EmployeeNo      DaysOffSick     DateOfSickness 
  2.  --------------  --------------  ---------------- 
  3.  001             1.00            1997-11-17 
  4.  002             1.00            2000-02-18 
  5.  002             1.00            2000-02-19 
  6.  002             1.00            2000-02-20 
  7.  003             1.00            1999-02-25 
  8.  003             1.00            1999-02-26 
  10.  -- note the dates increment and do not account for days off (eg. Saturday / Sunday) 


T-SQL concatenate an incremental row number

This is a quick article on how to use a counter added to the end of a field column of data.

Eh. What?
Assume the following two tables exist:

T-SQL Conversion failed when converting the varchar to data type int

A very quick note in case I forget this one. If you are trying to join two tables and receiving the error "Conversion failed when converting the varchar value 'B110' to data type int" then read on.


Latest Posts

  • 301 Redirect using htaccess file

    • Tue 15-Apr-14
      Further suggestion:
      RewriteRule ^(.*/)?assets/s 2dmain.html\?/( .*/)? $2 [R=301,L]
    • Thu 10-Apr-14
      Playing with some RegEx testers
      RewriteRule ^(.*\?/)?(?:$|( .+?)(?:(\.[^.]$ )|$)) $2 [R=301,L] ...
    • Wed 09-Apr-14
      I tried to redirect links from my old site to my new site, based on what I read in this thread. The old ...
  • Joes Revolver Map (JRM)

    • Tue 22-Apr-14
      Hi Manfred, Some downloaders have reported that the version of this module intended for Joomla 2.5.x ...
    • Sun 20-Apr-14
      Hello, is it possible to use this module also in joomla 3.2? Thanks Manfred
      Manfred V.