February 10, 2009 at 5:05 am
Hi
we have a table with 2 columns:
- 1 column with a description containing placeholders for parameters in the format {n}
- 1 column with the parameters in an XML format
How can we combine the columns in a single select statement to produce a readable description?
Example:
col1= User {0} logged {1}.
col2=
We need: User Kevin logged off.
My guess is this cannot be done in a single select but maybe someone out there knows better...
Thierry
February 10, 2009 at 6:11 am
Well if both fields are of a character type (not a number) it's rather simple. I feel like I am missing something in your post, because you stated one column has XML formatted data. Here is what I could recommend,
Select 'User ' + Col1 + ' has Logged ' + Col2 from Table1
Then just use a where clause to limit the data.
February 10, 2009 at 7:53 am
I think it would be possible to do this in a single query by using XQuery on the XML in the col2 column to extract the parameter values and recursive CTEs to perform the placeholder string replacements, but it may not be the best option.
You might consider a CLR function to do this.
February 10, 2009 at 10:19 pm
Hi again
oh yes, I forgot XML strings are not displayed in the web page
This is what it should be:
<parameters><parameter0>Kevin</parameter0><parameter1>off</parameter1></parameters>
I was thinking about XQuery as well but I'm not too familiar with it.
Both columns are nvarchar
February 11, 2009 at 8:25 am
You haven't provided your full table schema, so in the following I've assumed a table named XmlTemplate as follows. I've assumed there is primary key field (id) on this table to simplify the joins in the query.
CREATE TABLE XMLTemplate (
id Int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
col1 nvarchar(4000) NOT NULL,
col2 nvarchar(4000) NOT NULL
/* ...any other columns */
)
GO
Here's some test data
INSERT INTO XMLTemplate
SELECT N'User {0} logged {1}.',
N'<parameters><parameter0>Kevin</parameter0><parameter1>off</parameter1></parameters>'
UNION ALL SELECT N'User {0} logged {1} at {2}.',
N'<parameters><parameter0>Brian</parameter0><parameter1>on</parameter1><parameter2>09:34</parameter2></parameters>'
UNION ALL SELECT N'Exception raised.', N'<parameters/>'
The first CTE (cteFR) shreds the XML in col2 to generate a set of find/replace pairs for each parameter. The second CTE (cteReplace) recursively invokes the REPLACE function to replace the placeholders in col1 with the appropriate XML parameter values. The final SELECT statement filters out all the intermediate results that have accumulated in the cteReplace CTE.
I think that you may have performance issues with this approach and that a CLR function could be a better solution than any TSQL query.
;WITH cteFR AS (
SELECT
[Id] = X.Id,
[Step] = T.N,
[Source] = X.col1,
[Find] = N'{' + CONVERT(nvarchar(10), T.N) + N'}',
[Repl] = CONVERT(xml, X.col2).query('/parameters/*[local-name()=concat(''parameter'', string(sql:column("T.N")))]').value('.', 'nvarchar(1000)')
FROM XmlTemplate X
CROSS JOIN Tally T
WHERE (T.N >= 0 AND T.N < CONVERT(xml, X.col2).query('count(/parameters/*)').value('.', 'int'))
),
cteReplace AS (
SELECT Id = X.Id, NextStep = 0, Result = X.col1
FROM XmlTemplate X
UNION ALL
SELECT Id = FR.Id, NextStep = R.NextStep + 1 , Result = REPLACE(R.Result, FR.Find, FR.Repl)
FROM cteFR FR
INNER JOIN cteReplace R ON (FR.Id = R.Id AND FR.Step = R.NextStep)
)
SELECT R.Result
FROM cteReplace R
INNER JOIN (
SELECT Id, MaxStep = MAX(NextStep)
FROM cteReplace
GROUP BY Id
) RA ON (R.Id = RA.Id AND R.NextStep = RA.MaxStep)
February 12, 2009 at 2:58 am
thx a million Andrew! Your assumptions were right by the way. Actually this question came to me from via a colleague dealing with the SCOM 2007 database (I will not take the credits for this solution :))
My first thought was also CLR. When I saw the {n} parameter-style I immediately thought of C# String.Format. Probably MS manipulates this data in SCOM in the same way.
I'll have a look at CLR if my colleague is not happy with the performance.
Thx again!
Thierry
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply