SQL Server, avoid encoding special character when using FOR XML PATH

From SQL 2005, we are using FOR XML PATH('') for the string concatenation. I have also mentioned the same in very old blog: Generate Comma Separated List with SELECT statement. 

Today, we face an issue when special character gets encoded and we hate to have special character in my string. 

e.g: Table has records like: 
1. Gynecology & Obstetrics 
2. Dermatology 

Expected output (after concatenating two rows): Gynecology & Obstetrics, Dermatology. 

When we used FOR XML PATH, we got the output as: 
Gynecology & Obstetrics,Dermatology ("&" character is encoded and it lists out "&")

This output was not the one which we were expected. To get the expected output, we need to find workaround and we finally get it as: 

SELECT STUFF(((
        SELECT ',' + RTRIM(CONVERT(VARCHAR(50), sm.Speciality))
        FROM Table1 T2
        INNER JOIN Table2 sm ON sm.ID = t2.Speciality_ID
        FOR XML PATH(''),TYPE).value('.[1]', 'varchar(max)')
    ), 1, 1, '')

After adding "TYPE", we are able to get the expected output: Gynecology & Obstetrics, Dermatology 

Reference : Tejas Shah (http://www.SQLYoga.com)

Read More

SQL SERVER SSIS: Basic Example Of Data Flow Task

Today I am going to give Basic example of Data Flow Task in SSIS, import CSV file to SQL SERVER Database Many times user asked to import their data from their files. They might have data in Microsoft Access, Microsoft Excel, CSV File, Text Tile etc.. Microsoft SQL SERVER, SSIS, has features to import data from any heterogonous format to SQL and also capabilities to export it to any format. That is the power of SSIS. 1. Drag Control: Drag Data Flow Task from Toolbox, to Design interface 2. Configure Data Flow Task: Once you have Data Flow Task on Design surface, Double click on it, or Right click on task and click on "Edit", it will redirect you to Data Flow Tab, as shown in Figure 2:   As shown in above figure, SQL SERVER SSIS give features to import/export data from/to many formats. You can find the different formats that is supported by SSIS to import data on LEFT PANE. 3. Configure Source Connection: Here, I have selected "Flat File Source", as we need to import data from CSV File. Drag "Flat File Source" to design surface and right click on edit and select "Edit", you will get screen like:   As shown in figure, Click on "New", It will open a form which allows us to select file which we want to import to our Database. First, Enter connection manager name and description. Then, select a file which you want to import to database. Then, Select Locale and Code page as per your requirement, For this demo, I don't need to change anything. Then, we need to select Format of the file. SSIS provide us three formats: Delimited, Fixed Width, Ragged Right. We need to import CSV file (Comma Separated file), so I have selected "Delimited". Then, We need to specify Text Qualifier, Select based upon your requirements. Then, we need to specify delimiter for Header row, as we have CSV  file, I have selected CR LF, means new row for header. Then, SSIS also give us features to skip rows. By Header Rows to skip, it allows us to skip n number of rows. At Last, If there is Header in column names, we just need to select this checkbox, "Column names in the data row". So SSIS understands to set the first row as Header row. That's it for configure file, Now we need to specify delimiter for column, which comes on next section called "Columns"   As shown in figure, Now we need to specify delimiter for Rows and Columns. As we are going to import CSV file (comma separated file), Row Delimiter is "{CR}{LF}" and column delimiter is ",". NOTE: Also notice that, it display Column Names in Header, as we have checked, "Column Names in the First data row". This is the data that we are going to import to SQL Database. You can use Advanced and Preview tab, to configure each column and preview, respectively. Now once, configuration is done, we need to select OLE DB Destination, as we need to import it to SQL SERVER database. 4. Configure Destination Connection: Here, I have selected "OLE DB Destination", as we need to import data from CSV File to SQL SERVER Database. Drag "OLE DB Destination" to design surface.   Now, we need to design data flow. Here we need to import data from Flat File connection, I have dragged "Success" (Green Arrow), link to OLE Db Destination for column Mapping. Now, we need to configure OLE DB destination, to insert data to SQL Database. Right click on it and click on "EDIT", it will open screen like this:   I have entered SQL server Login information to connect to SQL SERVER where I need to import data from CSV file. Once, SQL SERVER is configured, we need to select Table Name to which we need to insert data from CSV file. See the following screen, I have selected "Employee" Table: Please find script to create "Employee Table" CREATE TABLE [dbo].[Employee](     [EmpID] [int] IDENTITY(1,1) NOT NULL,     [EmployeeName] [varchar](max) NULL,     [EmployeeCode] [varchar](max) NULL,     [EmployeeDesignation] [varchar](max) NULL,     [CreatedDate] [datetime] DEFAULT GETDATE() ) Now we have selected SQL SERVER table to which we need to import data, Now we need to map columns names, so SSIS will insert data accordingly. By default, SSIS provides mapping for columns which have same name, In this case Column Mapping is ok, so we don't need to do anything. If you want to change mapping, you can do it by just changing links between Input Columns and Destination Columns. NOTE: Here Destination table has two more columns, EmpID and CreatedDate, which is not available in source file, CSV file. We can just ignore it, as EmpID is Identity column while CreatedDate is GETDATE(). We have such columns in SQL database to keep track when this record is added. That's it. We have configured Data Flow Task. Now just right click on package and "Execute Package". You will find that Employee table will be populated with information from CSV file. Let me know if you have any questions in designing SSIS Package.

Read More

SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.

 

Example:

I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.

 

Proposed Solution:

Convert VARCHAR variable to XML as follows:

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

So this statement will generate XML from VARCHAR value as follows:

  1
  6
  7
  8
  20

So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:

SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)

This will give me result set as: ( as separate table)

So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.

So my Procedure looks like:

CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

UPDATE    TableName
SET        Flag = 1
WHERE    ID IN(
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
            )
            

 

So, it is very easy to read values from Comma separated value.

 

Let me know if it helps you.

Read More

SQL SERVER: Generate Comma Separated List with SELECT statement

Today I have the following situation, where I need to display all related data in comma separated list.

 

Till today we are using scalar function to display Comma separated list with select statement. That scalar function use the COALESCE() to make comma separated list. Today I found wonderful solution to display comma separated list without scalar function. Let see that.

 

Example:

I have Table like:

CREATE TABLE #test(
field1 VARCHAR(5), field2 VARCHAR(5)
)

Lets insert some data in this table:

INSERT INTO #test
SELECT '001','AAA'
UNION ALL
SELECT '001','BBB'
UNION ALL
SELECT '002','CCC'
UNION ALL
SELECT '003','DDD'
UNION ALL
SELECT '004','EEE'
UNION ALL
SELECT '004','FFF'
UNION ALL
SELECT '004','GGG'

So now my table has Data like:

Expected Output:

Proposed Solution:

SELECT field1,
 SUBSTRING( 
 (
  SELECT ( ', ' + field2)
  FROM #test t2 
  WHERE t1.Field1 = t2.Field1
  ORDER BY t1.Field1, t2.Field1
  FOR XML PATH('')
 ), 3, 1000)
FROM #test t1
GROUP BY field1

My Output will be:

 

Please make comments, if this helps you in any way

Read More