Tag Archives: Microsoft SQL Server

How To Flatten Xml Data in SQL Server

In this article we will see how we can flatten a xml data and make it more readable. Let us understand the scenario first and then dive into the solution. To understand the scenario we will create a xml file. Load the data in the file into a table in SQL Server.

Step 1:  Create a xml file and store it in the C drive. Below is the content of the file

<?xml version="1.0" ?>
<Root>
<Person>
<Name>John</Name>
<Age>30</Age>
<Hobbies>
<Hobby>PhotoGraphy</Hobby>
<Hobby>Driving</Hobby>
</Hobbies>
</Person>
<Person>
<Name>Wayne</Name>
<Age>21</Age>
<Hobbies>
<Hobby>Fishing</Hobby>
<Hobby>Cooking</Hobby>
</Hobbies>
</Person>
</Root>

Step 2 : Save the file as XmlSource.xml on the C drive. You can choose a different location also.

Step 3: Run the following script and create a table which will store the xml data.

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'XmlTable')
DROP TABLE XmlTable
go
CREATE TABLE XmlTable
(
XmLId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY ,
XmlData XML NOT NULL
)

Step 4: Run the below script to insert the contents of the xml file into the table

INSERT INTO XmlTable(XmlData)
SELECT * FROM OPENROWSET(BULK 'C:\XmlSource.xml', SINGLE_BLOB)
AS ImportSource
GO

Step 5: Check if the data has been inserted correctly or not.

xml1

 

If we observe we will see that the contents of the xmlData row are in xml format. So how do we make it something like below
xml2

Here you go. The below query will make it happen

SELECT pref.value('(Name/text())[1]', 'varchar(50)') AS Name ,
pref.value('(Age/text())[1]', 'varchar(50)') AS Age,
pref.value('(Hobbies/Hobby/text())[1]', 'varchar(50)') AS Hobby1,
pref.value('(Hobbies/Hobby/text())[2]', 'varchar(50)') AS Hobby2 
FROM
XmlTable
CROSS APPLY
XmlData.nodes('//Person') AS People ( pref )

The quick way to extract the xml data is to use the CROSS APPLY operator which uses a function
against each row and then adds the data to the result set. We then combine this with a function
that can be executed on the XML data type called NODES

 

 


			
Advertisements

How to Rebuild the MSDB database

I was preparing a migration plan to migrate all SQL Server databases from our existing SAN to new SAN. While doing so I thought of actually doing a small practice on my laptop SQL Server. The user databases moved from the existing location to another location very smoothly. I was very happy. Now was the turn of the system databases. I started with the MSDB database. As recommended in the Microsoft technet article of relocating System DB’s, I was following the steps prescribed. Pretty simple steps. After making the file location change in the master database, I stopped the SQL service, physically moved the files to the new location and then restarted the SQL service. The MSDB database would not come up….In recovery…..

What do I do?…I didn’t even have a backup of the MSDB database. I read few tech net articles that helped me rebuild the MSDB database from scratch. Here is what needs to be done

1) Stop all SQL server services.
msdb12) Start SQL Server from the command prompt using the trace flag T3608. The exact command for this would be : NET START [Name of the SQL service]/T3608. This is what the trace flag does as per Technet.

Prevents SQL Server from automatically starting and recovering any database except the master database. If activities that require tempdb are initiated, then model is recovered and tempdb is created. Other databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work.

msdb23) Now we need to detach the MSDB database from the instance. This is what needs to be done for the same. We need to run a SQLCMD command to do the same.
SQLCMD -E -S<servername> -dmaster -Q”EXEC sp_detach_db msdb”
msdb4After running this rename the existing MSDB files. Stop and restart SQL Service via the configuration manager.
msdb54) We need to locate the following .SQL file in the install folder of the SQL Server installation files.
msdb65) Open the script in SSMS and execute.

SQL Server agent was running successfully. The MSDB database was online again. The first thing I did was take a back up now.

 

 

 

SQL Server Integration Services – Foreach Loop Container

In my previous article I wrote about the For loop Container. In this article I will show how we can implement the Foreach loop container with a demo. The Foreach loop container is the third container amongst the three containers in SSIS.

The Foreach loop container can be used to loop through a set of enumerated objects like files in a folder or rows in a table. We can add different control flow tasks within the container for each enumerator or a DataFlow task to process data related to each item. Let us now implement this container with a small demo.

Implementing Foreach Loop Container

Objective: To copy files from one folder to another folder.

Step 1: We will be using the package we created in my article. Drag and drop a Foreach loop container in the design UI surface -> Drag and drop a File system task inside the container.
IS48Step 2 : Create a variable as shown below with the depicted data type and value
IS49Step 3: create 2 folders named ‘Source’ and ‘destination’ respectively. In the source folder create two files named file1.txt and file2.txt
IS50 IS51Step 4: Right click -> Choose Edit on the Foreach loop container and fill up the following as depicted below. Click on OK.
IS52 IS53Step 5: Right Click on the File System task -> On DestinationConnection -> Choose the usage type as ‘Existing Folder’ -> Choose the path of the destination folder -> click on ok.
IS54Step 6: Set the rest of the fields as below -> Click on OK.
IS55Now we are ready to test out solution. Press F5 and the 2 files should be in the destination folder.
IS56 IS57Bingo!!!! The files has been copied to the destination folder. I hope this article was useful in understanding the Foreach loop and its understanding.

SQL Server Integration Services – Control Flow Objects

In my previous article on SSIS we spoke about Packages,Connections and Data Sources. In this article we will talk about Control Flow Objects and how we can use them.

So what is Control Flow?

Control Flow is the engine which manages the workflow of the tasks created coupled with the control flow containers and constraints. SSIS provides a Design UI which displays a workspace where we can configure different control flow objects.

SSIS provides three types of control flow objects
1) Tasks – Tasks are objects that perform specific work
IS16
2) Containers – Containers help group different tasks
IS17
3) Constraints – Constraints help connecting different tasks and containers. Its also helps to define the order of execution of different tasks.
IS18

A package must contain at least one task that performs a certain operation. When multiple tasks are configured, then a container can be used to group them together.
IS19

 

Container

SSIS provides three types of Containers. Here is a brief description about the three Containers. We will deep dive into the Containers later.

1) Sequence Container : This allows us to group multiple tasks together. The benefits of this feature is that we can control the entire group rather that managing each task separately. It also means we can run different tasks sequentially or parallel. The entire group can collapsed or expanded. The Sequence container is the 3rd container in the toolbox
IS202) For Loop Container : This provides the same functionality as the sequence container but also lets you run tasks multiple times based on the condition provided. The For Loop is the 1st tool in the tool box
IS213) Foreach Loop Container : This allows looping but instead of a condition the looping happens over objects such as files and folders or tables in a database.
IS22

Implementation of Control Flow Task

We will now implement the above by creating and editing a control flow task and executing the package within the SSIS environment. We will be using the same project we created in our previous article for this demo.

Step 1: Open the Project using BIDS -> Select Mypackage.dtsx -> Click on the Toolbox -> Drag and Drop the Execute SQL Task on the Designer Surface. Rename the task as Update Table
IS23 IS24
Step 2: Right Click on the Task – > Click on Edit -> Change the Connection to AdventureWorks -> Click on the SQLStatement and insert the below query – > Click on OK.
IS25 IS26 IS27Query:

UPDATE Production.ScrapReason SET
ModifiedDate = '2014-06-01 00:00:00.000'
WHERE name = 'Drill pattern incorrect'

Step 3: click Start Debugging on the Standard toolbar -> If the execution is correct then the task would be green.
IS28 IS29Step 4: Click on the Progress tab to view the execution details of the task. -> Stop the execution by clciking on the stop debugging button – > Save the project.
IS30 IS31

The objective of this article was an introduction to the concept of Control Flow and Tasks with a small demo. I hope I was able to do that. In my next article I will write about Container with a broader perspective.

 

 

Using TIMEFROMPARTS() – SQL Server 2012

This new function in SQL server 2012 helps in converting time parts to time. The syntax of this function is as follows

TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

The range of the parameters for TIMEFROMPARTS is as follows :
Hour -> 0-23.
Minutes -> 0-59.
Seconds -> 0-59.
Fractions -> 0-9999999.
Precision -> 0-7.

Let us understand this with an example.

DECLARE @hour INT, @min INT,@Sec INT,@frac INT;
SET @hour = 13
SET @min = 24
SET @Sec = 22
SET @frac = 45
SELECT TIMEFROMPARTS(@hour,@min,@Sec,@frac,2)

time1

This function requires a valid value for the Hour,Minute, Seconds, Fractions, Precision parameters. If any invalid value is passed then this function will return an error. If a Null value is passed on for the Precision parameter then it generates an error. For other parameters if a Null is passed then the output is also Null.

For example in the below code we pass an invalid value for the Hour parameter

time2

Now lets see what happens when we pass a Null value to the Fraction parameter

time3
 
 
 
 
 
 
   
   
   
      
If we fail to pass any one of the parameters then the following error message is returned.
time4

How to troubleshoot error – Msg 195, Level 15, State 10, Line 1 ‘TRY_CONVERT’ is not a recognized built-in function name

While running the following code on a database on my SQL Server 2012 instance I got the following error message

USE BB
go
SELECT TRY_CONVERT(xml, 'MyClub')

TCerr1The reason for this error is that this function TRY_CONVERT is 110 compatible. Which means it will only run on a database with 110 compatibility. Upon checking the compatibility level, I found the database to be on 90 or SQL Server 2005 compatible.
TCerr2Resolution:
I had to change the compatibility level to 110 and then ran the code with success.

TCerr3

TRY_CONVERT function in SQL Server 2012

TRY_CONVERT is one of the new conversion function introduced in SQL SERVER 2012. It returns a value converted to the specified data type if the conversion succeeds. Otherwise,it returns returns NULL value when it fails to convert to a requested data type. TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted

The syntax for TRY_CONVERT is as follows

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Let us now implement this function and see how it works with different examples

SELECT TRY_CONVERT(xml, 'Manchester United')
SELECT TRY_CONVERT(DATETIME, '02/18/2014 05:30',111)
SELECT TRY_CONVERT(INT, '40')

The output of the above as follows
TryCon1

Now let us see examples where this function might fail or throw an exception

SELECT TRY_CONVERT(xml, 40)

And the output is
TryCon2

SELECT TRY_CONVERT(DATETIME, '22/18/2014 05:30',111)

And the output is
TryCon3The first error is self explanatory. The second code gives an output of NULL because the date is an invalid date.

Now the question is how is TRY_CONVERT different from CONVERT function?

Lets understand with this example

SELECT CONVERT(DATETIME, 'ABC')

TryCon4CONVERT will give an error message stating the conversion failed.

SELECT TRY_CONVERT(DATETIME, 'ABC')
AS 'Result'

TryCon5TRY_CONVERT will give a NULL.

 

%d bloggers like this: