Simplest way to get data from Coma separated String

There are many ways you will get value from coma or any other separator string, but today I will show you simplest approach to get value from string by using XML.


DECLARE @String varchar(max),
@Splitchar char(1),
@Xmlvalue xml

set @String = ‘10,20,30,40’
set @Splitchar = ‘,’

SELECT @Xmlvalue = CONVERT(xml,’ <root> <Node> ‘ + REPLACE(@String,@Splitchar,'</Node> <Node>’) + ‘ </Node> </root>’)
SELECT [Value] = T.c.value(‘.’,’varchar(20)’)
FROM @Xmlvalue.nodes(‘/root/Node’) T(c)

 

 

 

 

 

 

 

 

 

I personally believe that , that’s simplest approach to get value from string which is coma or any other separated.

If you know any clear and good approach please give feed back so I can share it.

USE of XML datatype, Read XML and Write XML in Sqlserver

ML data type is very useful in SQLserver 2005 and 2008.

Check the below script and learn how to use XML data type.

Create Table Patient(FirstName Varchar(20),LastName Varchar(20))
insert into Patient
values(‘Jack’,’Sparrow’)
insert into Patient
values(‘Peter’,’Parker’)
insert into Patient
values(‘John’,’Black’)

Below is the simple Feature of the XML.
You can set your table data in the XML type of variables
And read that.
Important feature is you can pass that XML type variables
To any procedure and you can read and process that data.
It’s like you pass table from one procedure to another procedure.

DECLARE @xmlDoc XML
SET @xmlDoc =
(
SELECT FirstName,LastName FROM Patient
FOR XML AUTO
)
SELECT @xmlDoc

Below code will fetch data from xml variable created above.

select
SelCat.CatDet.value(‘@FirstName’, ‘varchar(20)’) as FirstName,
SelCat.CatDet.value(‘@LastName ‘, ‘varchar(20)’) as LastName
from
@xmlDoc.nodes(‘Patient’) as SelCat(CatDet)

Below is the example of Write the rows to the XML TYPE
And you can read that as rows in any procedure.
This is useful when you have to pass block of rows
Without defining the table name.
Below is the example of that.

DECLARE @xmlDoc XML
SET @xmlDoc =
(
SELECT FirstName,LastName FROM Patient
FOR XML RAW
)

Below code will fetch data from xml variable created above.

SELECT
SelXML.xmlDetail.value(‘@FirstName’, ‘Varchar(20)’) AS FirstName,
SelXML.xmlDetail.value(‘@LastName’, ‘varchar(20)’) AS LastName
FROM
@xmlDoc.nodes(‘row’) AS SelXML(xmlDetail)
drop table Patient

I hope this post is useful for you and upgrade your knowledge.

How to get Data from Dataset into Sqlserver Table by using OPENXML Method?

Some time we need data from Dataset into Sqlserver for insertion or updation in the Sqlserver Tables.

For example if you need to insert an image then you can use this method to insert the data in Sqlserver tables.

Today, I will explain how to get data from Dataset into Sqlserver Table.

— Code for .Net

Dim ds as new Dataset

Dim dt as new Table

dt.TableName = “MyTable”

ds.Tables.add(dt)

Dim lStrWriter As New StringWriter

Dim lStr As String

ds.WriteXML(lStrWriter)

lstr = lStrWriter.Tostring

Pass this lstr variable to the procedure as parameter – @xmldoc.

Now write the code procedure to get the data from Dataset.

— Code in Sqlserver Store Procedure

CREATE PROCEDURE [dbo].[TestXML]

@xmldoc text — Give Text datatype for getting xml from dataset

as

———Read the Datset  From XML —————–

— call sp_xml_preparedocument

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

SELECT * into #TestTable

FROM OPENXML (@idoc, ‘/NewDataSet/MyTable’,2)

WITH (pk_Userid  BigInt,

UserName  Varchar(10))

EXEC sp_xml_removedocument @idoc

———- End Read ———-

END

BY the use of this procedure you get all the data from dataset in to the “#Testtable” and we can use this as Sqlserver table in procedure.

I hope this article will be useful to you, Please give feedback on this article.