SQL Server Portal

I have been using IN clause with multiple values from a long time. But I was always using it with string concatenation along with single quotes to make it string and then process it.
Recently, I found another solution , it works without string concatenation.

Let me explain this issue demonstrating simple example.

CREATE TABLE [dbo].[Student](
[Student ID]   [varchar] (6) Not NULL ,
[Student Name] [varchar](50) NOT NULL)
GO

Insert into dbo.[Student] values ('STD001','Bob')
Insert into dbo.[Student] values ('STD002','Alexander')
Insert into dbo.[Student] values ('STD003','Hosanna')
Insert into dbo.[Student] values ('STD004','William')
Insert into dbo.[Student] values ('STD005','Hulda')
Insert into dbo.[Student] values ('STD006','Jacoba')

Old Approach :

Declare  @SQL         VARCHAR(MAX)
Declare  @WhereClause VARCHAR(MAX)
Set @WhereClause='Bob,Hulda,Jacoba'

SET @WhereClause=REPLACE(@WhereClause,',',''',''')
Set @SQL='Select *
from dbo.[Student]
Where  [Student Name] In (''' + @WhereClause + ''')'

EXEC (@SQL)

New Approach :

Declare  @Xml AS XML
Declare  @WhereClause VARCHAR(MAX)
Set @WhereClause='Bob,Hulda,Jacoba'
SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML)
Select *
from dbo.[Student]
Where  [Student Name] In (
SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)
)

Explanation :

In the new approach, I need to explain these two important lines of code.

SET @Xml = cast(('<A>'+replace(@WhereClause,',' ,'</A><A>')+'</A>') AS XML)
Select @Xml 

This code snippet will convert string into XML, replacing the delimiter with start and end XML tag.

(Here, It is used as <A></A>).

Output :

SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)

Once the string is converted into XML,By using this code snippet, we can easily query it via xquery.
Output :

I’d really appreciate your comments on my posts, whether you agree or not, do comment.

Read Full Post »