May 22, 2009 at 4:49 am
declare @csv varchar(MAX) -- the CSV we are being passed
set @csv = '1,2,3,7,4,8,10' -- our list of CSV values
I WANT TO CONVERT IT INTO TABLE LIKE
VALUE
------
1
2
3
7
8
10
May 22, 2009 at 5:20 am
Hi shantaram,
You can use Tally table method as described in "Numbers or Tally Table"
article by Jeff Gordan on this site.There might also be other solutions by using loop.But for starters you can try and run this code -
declare @csv varchar(MAX) -- the CSV we are being passed
set @csv = '1,2,3,7,4,8,10' -- our list of CSV values
set @csv=','+ @csv+ ','
create table #ValueTable(N int,value varchar(100))
select identity(int,1,1) as N into #tally
FROM Master.dbo.SysColumns sc1
insert into #ValueTable
select N,substring(@csv,N+1,CHARINDEX(',',@csv,N+1)-N-1)
from #tally
WHERE N < LEN(@csv) AND SUBSTRING(@csv,N,1) = ','
select value from #ValueTable
drop table #ValueTable
drop table #tally
best of luck
May 22, 2009 at 5:48 am
Thanks Hitendra for ur quick response
But I need something in the way like below, since my csv may include Characters/words.
declare @idoc int -- the handle for the XML document
declare @csv varchar(MAX) -- the CSV we are being passed
set @csv = 'Roopa,Abhay,Seema,Balloo,Chetan,Dhiren,Rajiv,Zebra'
-- or '1,2,3,7,4,8,10'
-- our list of CSV values
EXEC sp_xml_preparedocument @idoc OUTPUT, @csv
SELECT *
FROM OPENXML (@idoc, '/stringarray/element',1)
WITH (value int)
EXEC sp_xml_removedocument @idoc
Though its not working fine because some silly prob, I need the query in xml way.
Thanks in anticipation.
May 22, 2009 at 6:45 am
Its Done now with following query:
SELECT seqno AS SrNo, item as Value
FROM OPENXML (@idoc, '/stringarray/element', 2)
WITH (seqno VARCHAR(MAX),
item VARCHAR(MAX))
May 22, 2009 at 7:00 am
Hi Shantaram,
The given code logic i had posted earlier would work well for characters strings as well, if it is written in comma separated list.
As per the given code:
"
declare @idoc int -- the handle for the XML document
declare @csv varchar(MAX) -- the CSV we are being passed
set @csv = 'Roopa,Abhay,Seema,Balloo,Chetan,Dhiren,Rajiv,Zebra'
-- or '1,2,3,7,4,8,10'
-- our list of CSV values
EXEC sp_xml_preparedocument @idoc OUTPUT, @csv
SELECT *
FROM OPENXML (@idoc, '/stringarray/element',1)
WITH (value int)
EXEC sp_xml_removedocument @idoc "
I guess you need to pass xml document based content in @csv
such as "Roopa....."
Apart from that use - 'SELECT ref.value ('first-name', 'nvarchar(64)'
FROM docs CROSS APPLY xCol.nodes ('/book/author) R(ref)' for retrieving names from the xml document.
Can you please provide me with your entire script, so that i can better understand it?
May 22, 2009 at 7:00 am
Hi Shantaram,
The given code logic i had posted earlier would work well for characters strings as well, if it is written in comma separated list.
As per the given code:
"
declare @idoc int -- the handle for the XML document
declare @csv varchar(MAX) -- the CSV we are being passed
set @csv = 'Roopa,Abhay,Seema,Balloo,Chetan,Dhiren,Rajiv,Zebra'
-- or '1,2,3,7,4,8,10'
-- our list of CSV values
EXEC sp_xml_preparedocument @idoc OUTPUT, @csv
SELECT *
FROM OPENXML (@idoc, '/stringarray/element',1)
WITH (value int)
EXEC sp_xml_removedocument @idoc "
I guess you need to pass xml document based content in @csv
such as "Roopa....."
Apart from that use - 'SELECT ref.value ('first-name', 'nvarchar(64)'
FROM docs CROSS APPLY xCol.nodes ('/book/author) R(ref)' for retrieving names from the xml document.
Can you please provide me with your entire script, so that i can better understand it?
May 22, 2009 at 7:41 am
/*
AUTHOR : SHANTARAM
DATE : 21/05/2009
PURPOSE : CONVERTING ARRAY VALUE INTO TABLE EXPRESSION
SAMPLE :
1.
EXECUTE dbo.usp_ArrayToTable '1.2.3.7.4.8.10', '.'
2.
CREATE TABLE #ParsedArrays
(
SrNo VARCHAR(MAX),
Value VARCHAR(MAX)
)
INSERT INTO #ParsedArrays EXECUTE dbo.usp_ArrayToTable 'Abhay|Balloo|Chetan|Dhiren|RAJIV|GAIKWAD', '|'
SELECT * FROM #ParsedArrays
*/
ALTER PROC dbo.usp_ArrayToTable
(
@ArrayString varchar(MAX), -- the CSV we are being passed
@tSeparater VARCHAR(10)
)
AS
BEGIN
--declare @ArrayString varchar(MAX) -- the CSV we are being passed
DECLARE @idoc int -- the handle for the XML document
--set @ArrayString = '1,2,3,7,4,8,10'
--set @ArrayString = 'Abhay,Balloo,Chetan,Dhiren,RAJIV,GAIKWAD'
SELECT @ArrayString = CONVERT(VARCHAR(MAX), dbo.array(@ArrayString, @tSeparater))
EXEC sp_xml_preparedocument @idoc OUTPUT, @ArrayString
SELECT seqno AS SrNo, item as Value
FROM OPENXML (@idoc, '/stringarray/element', 2)
WITH (seqno VARCHAR(MAX),
item VARCHAR(MAX))
EXEC sp_xml_removedocument @idoc
END
----------------------------------------------------------
GO
----------------------------------------------------------
CREATE FUNCTION [dbo].[array]
-- =================================================
-- array Function
-- =================================================
-- This function returns an XML version of a list with
-- the sequence number and the value of each element
-- as an XML fragment
-- Parameters
-- array() takes a varchar(max) list with whatever delimiter you wish. The
-- second value is the delimiter
(
@StringArray VARCHAR(8000),
@Delimiter VARCHAR(10) = ','
)
RETURNS XML
AS BEGIN
DECLARE @results TABLE
(
seqno INT IDENTITY(1, 1),-- the sequence is meaningful here
Item VARCHAR(MAX)
)
DECLARE @Next INT
DECLARE @lenStringArray INT
DECLARE @lenDelimiter INT
DECLARE @ii INT
DECLARE @xml XML
SELECT @ii = 0, @lenStringArray = LEN(REPLACE(@StringArray, ' ', '|')),
@lenDelimiter = LEN(REPLACE(@Delimiter, ' ', '|'))
WHILE @ii <= @lenStringArray + 1--while there is another list element
BEGIN
SELECT @next = CHARINDEX(@Delimiter, @StringArray + @Delimiter,
@ii)
INSERT INTO @Results
(Item)
SELECT SUBSTRING(@StringArray, @ii, @Next - @ii)
SELECT @ii = @Next + @lenDelimiter
END
SELECT @xml = ( SELECT seqno,
item
FROM @results
FOR
XML PATH('element'),
TYPE,
ELEMENTS,
ROOT('stringarray')
)
RETURN @xml
END
GO
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply