Re: From varchar(max) to xml

Ooops, the "syscomments" references will need to be changed for SQL 2005 to
"sys.comments".

"Mike C#" <xyz@xxxxxxx> wrote in message
news:ORxRYYwlGHA.2056@xxxxxxxxxxxxxxxxxxxxxxx
I can't think of a nice set-based method of doing this, but you could use
procedural code to do it. I agree with Denis, this should probably be done
in the front end. But that said, here's a little procedural sample. Note
that I don't have SQL 2005 on the computer I'm at right now so I had to put
this thing together on SQL 2000. It should run properly on SQL 2005 as
well. It's *extremely* procedural and assumes that the TestInput table has
a numeric id for each row, row 0 being the column names and all other rows
containing data. The procedural nature of this type of code makes me think
you'd be a lot better off doing it on the front end though:

-- Create a "Numbers" table and an inline UDF that uses it to parse your
-- comma-delimited string. Run this section one time.
SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
INTO Numbers
FROM syscomments a1
CROSS JOIN syscomments a2
-- Add Primary Key to Numbers table
ALTER TABLE Numbers
ALTER COLUMN Number INT NOT NULL
ALTER TABLE Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
-- Create inline UDF
GO
CREATE FUNCTION dbo.ParseDelimitedList (@list AS NVARCHAR(4000))
RETURNS TABLE
AS
RETURN (
SELECT Number, LTRIM(RTRIM(CASE Number
WHEN 1 THEN SUBSTRING(@list, 1,
CASE WHEN CHARINDEX(';', @list, Number + 1) > 0 THEN
CHARINDEX(';', @list, Number + 1) - 1
ELSE LEN(@list) - CHARINDEX(';', @list, Number + 1)
END)
ELSE SUBSTRING(@list, Number + 1,
CASE WHEN CHARINDEX(';', @list, Number + 1) > 0 THEN
CHARINDEX(';', @list, Number + 1) - Number - 1
ELSE LEN(@list)
END)
END)) AS Value
FROM Numbers
WHERE (SUBSTRING(@list, Number, 1) = ';' OR Number = 1)
)
GO
-- End of the Numbers table/UDF initialization.

CREATE TABLE TestInput([id] INT PRIMARY KEY,
a VARCHAR(8000),
b VARCHAR(8000))

INSERT INTO TestInput([id], a)
SELECT 0, 'Col1;Col2'
UNION SELECT 1, 'New York;USA'
UNION SELECT 2, 'Rio;Brasil'
UNION SELECT 3, 'Tokio;Japan'

DECLARE @sql VARCHAR(8000)
DECLARE @temp_str VARCHAR(8000)
DECLARE @cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[col_name] VARCHAR(8000))

SELECT @temp_str = a
FROM TestInput
WHERE [id] = 0

INSERT INTO @cols([col_name])
SELECT Value
FROM dbo.ParseDelimitedList(@temp_str)
ORDER BY [Number]

DECLARE @col_count INT
SELECT @col_count = MAX([id_num])
FROM @cols

DECLARE @vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[value] VARCHAR(8000))

DECLARE @id INT
SELECT @id = 1

DECLARE @i INT

WHILE @id <= (SELECT MAX([id]) FROM TestInput)
BEGIN
SELECT @temp_str = a
FROM TestInput
WHERE [id] = @id

IF NOT(@temp_str IS NULL)
BEGIN
INSERT INTO @vals([value])
SELECT [Value]
FROM dbo.ParseDelimitedList(@temp_str)
ORDER BY [Number]

SELECT @temp_str = ''
SELECT @i = 1
WHILE @i <= @col_count
BEGIN
SELECT @temp_str = @temp_str + '<' +
(
SELECT [col_name]
FROM @cols
WHERE [id_num] = @i
) + '>'
SELECT @temp_str = @temp_str +
(
SELECT COALESCE([value], '')
FROM @vals
WHERE [id_num] = @i + (@id - 1) * @col_count
)
SELECT @temp_str = @temp_str + '</' +
(
SELECT [col_name]
FROM @cols
WHERE [id_num] = @i
) + '>'
SELECT @i = @i + 1
END
UPDATE TestInput
SET b = @temp_str
WHERE [id] = @id
END
SELECT @id = @id + 1
END

SELECT *
FROM @vals

SELECT *
FROM TestInput





"psychodad71 via SQLMonster.com" <u2248@uwe> wrote in message
news:6232e7ee08949@xxxxxx
I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
Column a contains the following data:

Col1;Col2
New York;USA
Rio;Brasil
Tokio;Japan

The first line contains the column header, the following the data.

The data should be transferred to column b with the following
xml-structure:

<Col1>New York</Col1><Col2>USA</Col2>
<Col1>Rio</Col1><Col2>Brasil</Col2>
<Col1>Tokio</Col1><Col2>Japan</Col2>

The number of columns and the column names are various.

Any ideas?

Thanks psychodad71

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-xml/200606/1




.

Relevant Pages

  • Re: Views vs Stored Procedures, whats the difference?
    ... I'm just trying to differentiate between two fundamentally different SQL objects. ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ... SELECT MAXFROM nestedview ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • Re: Views vs Stored Procedures, whats the difference?
    ... In the proc I would write the MAX as you've done. ... @optional_parm1 int = NULL, ... While the above does contain logic, it will give you the best plan in MS SQL ... CREATE TABLE P(pk INT NOT NULL PRIMARY KEY, ...
    (comp.databases.ms-sqlserver)
  • Re: From varchar(max) to xml
    ... this thing together on SQL 2000. ... ADD CONSTRAINT PK_Numbers PRIMARY KEY ... CREATE TABLE TestInput(INT PRIMARY KEY, ... DECLARE @temp_str VARCHAR ...
    (microsoft.public.sqlserver.xml)
  • Re: how to use datetime range in prepare statement
    ... Hi David, ... coming from SQL Server and is complaining aboutPerhaps you could use SQL ... declare @P1 int ...
    (microsoft.public.sqlserver.jdbcdriver)