Sql Server XML Cheetsheet

Cheet sheet XML Features SQL Server

Pre-requirement: Knowledge of XPath

All the samples are based on this XML:

<MyBooks>
	<ListOwner>John Smith</ListOwner>
	<Book>
		<title published="1885">The Adventures of Huckleberry Finn</title>
		<author realName="Samuel Langhorne Clemens">Mark Twain</author>
	</Book>
	<Book>
		<title published="1894">Tom Sawyer Abroad</title>
		<author realName="Samuel Langhorne Clemens">Mark Twain</author>
	</Book>
</MyBooks>

Extract Values
select @xml.value(‘( /MyBooks/ListOwner )[1]’, ‘varchar(20)’);

Possible error:

error fix
‘value()’ requires a singleton (or empty sequence), found operand of type place the XPATH query between ‘()[1]’

Check if exists

select @xml.exist(‘ //Book[author/@realName=”Samuel Langhorne Clemens”] ‘)
select @xml.exist(‘ //Book[contains((author/@realName)[1],”Langhorne”)] ‘)

Extract Node

SELECT @xml.query(‘/MyBooks/Book’) –Results all book nodes.SELECT @xml.query(‘/MyBooks/Book[title=”The Adventures of Huckleberry Finn”]’)SELECT @xml.query(‘/MyBooks/Book[title/@published<1890]’)

Select nodes

This will allow you to run SQL queries on XML.

select t.c.value(‘(title)[1]’,’varchar(20)’) from @xml.nodes(‘ //Book ‘) as t(c)

Modify Xml

Namespaces:

When there are namespaces in you XML you will have to do additional work.

Retrieving Stored Procedures from SqlServer and version control them.

Here is a script that you could use to retrieve your stored procedures and store them in your custom folder structure.

what this script does:

  1. create a list of all stored procedures on disk (inc subdir)
  2. retrieve all stored procedures
  3. add some extra TSql around it ( like use database …)
  4. overwrite SP by name in your dir; IF this was not possible store the script in notsorted dir.

Hope you’ll like it.

@echo off
rem This script has been created to extract all Stored Procedures,
rem and place them in your version controlled directory structure.
rem Any script not stored will be placed in the 'notsorted' dir.

rem This scripts uses 'Intergrated security' the -E of osql. If you want to use log and pass use -U pass -P pass
rem ######################
set serverName= >>HostNameHere<<
set dbName= >>DataBaseNameHere<<
rem ######################


IF EXIST tmp rd tmp /s /q
IF EXIST notsorted rd notsorted /s /q

IF NOT EXIST tmp mkdir tmp
IF NOT EXIST notsorted mkdir notsorted

rem Create List of existing scripts (handy for version controlling)
dir *.sql /s /b >orgScripts.txt

osql -E -S %serverName% -d %dbName% -h-1 -Q "SET NOCOUNT ON;SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.Routines WHERE ROUTINE_TYPE = 'PROCEDURE'" -n -o ".\tmp\sp_list.txt"


rem Store one SP per file.
for /f %%a in (.\tmp\sp_list.txt) do (


	REM write to *._sql to match
	SQLCMD -S %serverName% -d %dbName% -Q"EXEC sp_helptext '[dbo].[%%a]'" -W -h"-1" -o .\tmp\%%a._sql


	REM This is basicly a template that comes at the start of the script.
	echo USE [%dbName%] > .\tmp\%%a._t_sql
	echo GO >> .\tmp\%%a._t_sql
	echo SET ANSI_NULLS ON >> .\tmp\%%a._t_sql
	echo GO >> .\tmp\%%a._t_sql
	echo SET QUOTED_IDENTIFIER ON >> .\tmp\%%a._t_sql
	echo GO >> .\tmp\%%a._t_sql

	rem Merge template and SP.
	copy .\tmp\%%a._t_sql +.\tmp\%%a._sql .\tmp\%%a.sql  >nul


	del .\tmp\%%a._sql
	del .\tmp\%%a._t_sql


	REM add last LINE.
	echo GO >> .\tmp\%%a.sql
)

rem Overwrite stored procedures with the ones in version control.
for /f %%a in (.\tmp\sp_list.txt) do (
	call :merge %%a.sql
)

goto end

:merge
set _Fndcmd=find /I "%1" orgScripts.txt
set _writes=0

FOR /f "tokens=*" %%b in ('%_Fndcmd%') do (
	IF EXIST %%b (
		IF EXIST .\tmp\%1 (
			rem DEBUG LINE:
			rem echo copy %1 to %%b
	
			rem maybe you need to remove readonly (use Attrib -r)
 			copy ".\tmp\%1" "%%b" >nul			
			set _writes=1
		)
	)
)
rem Is a file stored?
IF %_writes% EQU 0 (
	echo Not stored! [%1]  
	copy .\tmp\%1 .\notsorted\%1 >nul
)

goto :eof


:end
Rem remove temp dir.
rem rd tmp /s /q
del orgScripts.txt

 

Type.GetType, partial assembly names. Getting types of an assambly in the GAC.

This article demonstrates what you could do when loading a type of an assembly that resides in the GAC.

Consider this piece of code of a console application. (System.Drawing) is NOT referenced!

            //Find by partial Name.
            Type partial = Type.GetType("System.Drawing.Rectangle, System.Drawing"); 
            //Find type with Assambly identity
            Type byIdent = Type.GetType("System.Drawing.Rectangle, System.Drawing, version=2.0.0.0,publicKeyToken=b03f5f7f11d50a3a,culture=neutral");

            if (partial == null)
                Console.WriteLine("alter app.config to let this work");

            if (byIdent == null)
                Console.WriteLine("Not found in GAC!");

            Console.ReadKey();

There are 2 way’s to load a type with partial assambly name.
1) place the assambly in the executing directory, the file will be found. (Consider the installation implications!)
2) Alter your app.config to

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <qualifyAssembly partialName="System.Drawing" fullName="System.Drawing,version=2.0.0.0,publicKeyToken=b03f5f7f11d50a3a,culture=neutral"/>
    </assemblyBinding>
  </runtime>
</configuration>

Backup Database Server2Go while developing.

When you are developing with the Server2Go stack, you might want to automatically backup your database periodicly.
 
I propose a simple script that backups your database each time when you start up your Self configuring database.

Script:

@echo off

rem Add date to filename
for /f "tokens=2" %%i in ('date /t') do set DATE_DOWNL=%%i
for /f "tokens=1,2 delims=:" %%i in ('time /t') do set DATE_DOWNL=%DATE_DOWNL%_%%i%%j

echo dumping database to MySql_%DATE_DOWNL%.sql

.\server\mysql\bin\mysqldump --port=7188 -u root --all-databases >.\MySql_%DATE_DOWNL%.sql

 
Create a batch file with a name such as “backup.cmd” and place it in the same location where “server2go.exe” is located.
 
You will need to place it in your pms_config.ini :

[Startup]
;--- You can add additional EXE files that will be started in the startup process of Server2Go
;--- At the moment the EXE files will be started invisible and right AFTER the webserver
;--- and MySQL-Start
Startup1=backup.cmd
Startup2=
Startup3=

 
So every time you start Server2Go a backup will be made. Also as long Server2Go is running you can execute the batch file whenever you want to make an additional extra backup.

Please +1 When you are using it!

Profiling a Windows Service

You need to do a little bit extra to profile a service, if you get “VSPerfCLREnv was not set up correctly” solution is here.

This “howto” has been written for a really simple service, wich you can download. It’s tiny so you can isolate the “how do i” from the “for my service”.

To quickly start do this:

run “VSPerfCLREnv.cmd /globalsampleon” this cmd is located in “Team Tools\Performance Tools”(Vs2008/vs2010) install folder, for me that is : “C:\Program Files\Microsoft Visual Studio 9.0\Team Tools\Performance Tools\” . This will be “C:\Program Files\Microsoft Visual Studio 10.0\Team Tools\Performance Tools\” for vs2010.

If your Service starts at startup, reboot, else stop and start your service. I booted just to be sure.

Open your “windows service” project in Visual Studio.

When you are using my sample do this:

Compile solution (this installs the service and starts the service, see: “build event”)

Start Wizzard : Analyze > Launch Performance Wizzard , select “sampling” and finish.

Now you can’t “Launch with Profiling” (you can’t start a service directly), you need to “Attach” to it, find your service process. Check the “Show processes from all users” and “Show processes in all sessions“.

Run it as long as you wish until you are done. Press stop and you’ll receive report which you can drill down into.

Now in real live applications your service might not be a hog for cpu cycles. So you might get an error that “No Data was Collected“.

This could happen:

  • You did not attach to correct process!
  • Your app does so little, when the profiler does some sampeling your service is idle and therfore invisible to the profiler

Finally:

When you are done with profiling call “VSPerfCLREnv.cmd” /globaloff” this will disable profiling and give you back those cpu cycles otherwise used for profiling.