Query XML with SQL

I've just learned how to query an xml structure with SQL.
I'm presenting you a simple example. Assuming your XML column looks like this:

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.xyz.de/activerepository/fileprops">
  <props>
    <prop ns="ARM:" elem="_NoFilter">
      <value xsi:type="xsd:boolean">true</value>
    </prop>
    <prop ns="DAV:" elem="displayname">
      <value xsi:type="xsd:string">Some text in it</value>
    </prop>
    <prop ns="DAV:" elem="getcontenttype">
      <value xsi:type="xsd:string">message/rfc822</value>
    </prop>
    <prop ns="DAV:" elem="creationdate">
      <value xsi:type="xsd:dateTime">2017-01-02T09:38:28.1278078</value>
    </prop>
  </props>
</root>

...you can write this query to get single values from the XML. In my example, I want to query the displayname prop.

SELECT 
  CAST(properties as xml).value('(/root/props/prop[@elem="displayname"]/value)[1]','nvarchar(max)') as [displayname],
  *
FROM   

  tm_cas_files (nolock)

The result will be:

displayname

Some text in it

See also this Stackoverflow link to get more details:
https://stackoverflow.com/questions/48075328/sql-xml-how-to-query-specific-node

SQL - Use PARSENAME function to extract individual parts from a string

Today I discovered a very cool feature in T-SQL from Microsoft SQL Server.
It happens (unfortunately) again and again that several records are written in a single column and one is forced to extract individual parts from it. So far, I have always written a SQL function of my own, but this is will be history from now on with the help of the PARSENAME function. :-)

See this examples. I think, this explains it very well:

DECLARE @exampleString nvarchar(max) = 'Test;Test1;Test2' 
SELECT PARSENAME(REPLACE(@exampleString,';','.'),1)

--Result: Test2

SELECT PARSENAME(REPLACE(@exampleString,';','.'),2)

--Result: Test1

SELECT PARSENAME(REPLACE(@exampleString,';','.'),3)

--Result: Test

SQL - Fehler - ARITHABORT

Heute mal in Deutsch, da ich den genauen Wortlaut der englischen Fehlermeldung nicht kenne. :-)

Das Thema ist mal wieder SQL.

Ich hatte vor kurzem bei einem SQL Statement folgenden Fehler:

"Fehler bei SELECT, da die folgenden SET-Optionen falsche Einstellungen aufweisen: 'ARITHABORT'. Überprüfen Sie, ob die SET-Optionen für die Verwendung mit indizierten Sichten und/oder Indizes für berechnete Spalten und/oder gefilterte Indizes und/oder Abfragebenachrichtigungen und/oder XML-Datentypmethoden und/oder Vorgänge für räumliche Indizes richtig sind."

Dieser Fehler ist in der Regel recht einfach zu beheben. Es muss lediglich der Wert für ARITHABORT auf ON festgelegt werden und schon funktioniert die Abfrage (zumindest in meinem Fall :-) ).

SET ARITHABORT ON
...

Ich hoffe, ich euch damit viel Sucherei ersparen...

SQL Query - Get multiple row results in one column with XML and STUFF()

Today I want to show you, how you can put the results of an SQL query into one column.

First I create a temporarily table for our data and fill some data in it:

CREATE TABLE #table (product nvarchar(255)) 

INSERT INTO #table
SELECT 'product-1' as product
UNION 
SELECT 'product-2' as product
UNION 
SELECT 'product-3' as product
UNION 
SELECT 'product-4' as product


Now we have our base table for our query. Here are the results of this table

Query:
select * from #table

Results:
product
product-1
product-2
product-3
product-4

Now we can see, we have 4 rows with our example products. Maybe we have a requirement to put all products in one row. This can be done by using the STUFF function and XML.
Here is the code:

select STUFF((SELECT distinct ',' + t.product
                    from #table t (nolock)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'') as allProducts

Now the result of this query is this:

allProducts
product-1,product-2,product-3,product-4


Hope you enjoyed this lesson.

Code Snippet - SQL - Replacing line breaks with SQL query

If you have line breaks in an SQL string field and you want to replace or remove them, you can use the following code (bold marked):

UPDATE details SET user_text = REPLACE(REPLACE(user_text, char(10), char(32)), char(13), char(32));

SQL Server - Finding values in different columns with CROSS APPLY

I've found a nice way to check values in different columns in an SQL Server table and return only the columns where the search value is in.

In my example, we have a table with several columns (No1 - No7). We want to find out which columns have the value 8 in it and only return these ones.

Check the code:

First we create a table to check.

DECLARE @t TABLE (AdrNr INT, Nr1 INT, Nr2 INT, Nr3 INT, Nr4 INT, Nr5 INT, Nr6 INT, Nr7 INT)

Then we enter some values in it:

INSERT INTO @t
VALUES (500, 3, 4, 8, 42, 5, 76, 91)

This is the SQL code to return only the columns where the value "8" can be found:

SELECT AdrNr, a, b
FROM @t
CROSS APPLY (
    VALUES
        ('Nr1', Nr1),
        ('Nr2', Nr2),
        ('Nr3', Nr3),
        ('Nr4', Nr4),
        ('Nr5', Nr5),
        ('Nr6', Nr6),
        ('Nr7', Nr7)
) t(a, b)
WHERE b = 8

Tutorial - SQL Server 2014 Express - Job automation

Hi guys,

as you know, in Express Editions of Microsoft SQL Server the agent is not available. So it is difficult to create jobs, which run automated.
I've found a nice way via the command line to create automated SQL jobs without the SQL Server Agent.

First create your SQL statement and save it in an extra file. Name it i.e. "sqlCommand.sql".
Maybe you want to make a daily backup of your database, you create a statement like this, but it can be any valid SQL statement. This is just an example.

BACKUP DATABASE [db_myDatabase] TO  DISK = N'C:\Backup\SQL Server\db_myDatabase.bak' WITH NOFORMAT, INIT,  NAME = N'db_myDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'db_myDatabase' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'db_myDatabase' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''db_myDatabase'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM  DISK = N'C:\Backup\SQL Server\db_myDatabase.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

I you have Microsoft SQL Server 2014 Express installed, you can navigate via command line to the following directory:

"C:\Program Files\Microsoft SQL Server\110\Tools\Binn"

In this directory you find a executable named "sqlcmd.exe".

This executable you can use to execute your SQL Statement saved in your file "SqlCommand.sql".
Replace <server> with your server / machine name and <sqlInstance> with your instance of your SQL Server. The parameter -i tells the executable what SQL script should run. Replace it where your SQL script is saved.

sqlcmd -S <server>\<sqlInstance> -E -i "C:\Jobs\SQL Server\SqlCommand.sql"

When you press enter, the command(s) in your SQL script will be executed.
Now you just have  to create a simple command line script, which can be executed in a task scheduler job.

Command-line script:
c:
cd\
cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"
sqlcmd -S <server>\<sqlInstance> -E -i "C:\Jobs\SQL Server\SqlCommand.sql"


That's it. :-)

SQL Server 2008 - Creating an empty GUID

You can create a empty guid with this SQL code:

select CAST(0x0 AS UNIQUEIDENTIFIER) as [emptyGuid]

SQL Server 2008 - Get primary key in INSERT SQL statement

This tutorial is about to receive the primary key of a new inserted dataset.

declare @CreatedID bigint
exec sp_MyStoredProcedure parameter1, parameter2, @CreatedID OUTPUT
select @CreatedID

The stored procedure looks like this:
ALTER PROCEDURE [dbo].[sp_MyStoredProcedure]
@parameter1 int,
@parameter2 int,
@CreatedID BIGINT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

insert into 
tbl_MyTable
(
field1,
field2
)
values
(
@parameter1,
@parameter2
);
SELECT @CreatedID = CAST(SCOPE_IDENTITY() as bigint)
END

Transact SQL - Creating Temp Tables

This simple example shows you how to create and use temp tables in Microsoft SQL Server 2008.

First we have to create the temp table. The difference between regular tables and temp tables is to use the # before the table name. The syntax is the same:

create table #tempTable(keyword nvarchar(max), productNo nvarchar(30), anotherValue nvarchar(max))

After the temp table is created, we can put some data into it:

insert into #tempTable (keyword, productNo, anotherValue) 
 values ('test', 1234, 'somewhat')

After the data is in the temp table, we can use regular select statements:

select * from #tempTable

The result is:

keyword productNo anotherValue

test 1234 somewhat

SQL Server 2008 - Query XML data

Just recognized, that it is easy to query xml data stored in a SQL Server 2008 table (field):

declare @data xml

select @data = <datafield> from <table> (nolock) where <condition>

select @dataselect @data.query('<node>') as result

Here an example with sample xml:

declare @data xml

select @data = '<root><datas><data><add name="a" value="x1" /><add name="b" value="y2" /> <add name="c" value="z3" /></data><data><add name="a" value="e4" /> <add name="b" value="f5" /><add name="c" value="g6" /> </data></datas></root>' 

select @data

select @data.query('(/root/datas/data/add[@name="c"])[1]') as result 

The result is:

<add name="c" value="z3" /> 

Now I need to find out, how to get the value and not the complete node.