Thanks MM for your fast reply!
However I didn’t get it to work. Maybe problem is in Column1 data type, it is ntext. I read somewhere that it couldn’t be ntext type in declare procedure. Is it so?
You cannot declare a variable as ntext. If column1 is ntext, to get it working using mm's code you would do this:
declare @Table1 table(Column1 ntext);
values( '<instance><Title>New product</Title> <Date> 2013.10.24</Date><Author>TV</Author><Meter>LV</Meter><Result>90</Result ></instance>');
WITH ntext_to_xml(Column1) AS (SELECT CAST(Column1 AS xml) FROM @Table1)
Column1.value('(instance/Title/text())','varchar(255)') as title
, Column1.value('(instance/Result/text())','varchar(255)') as result
I have another way to do it: just have to merge two rows because now it is like that: (Ids are same in two pair rows)
Id key value
41 Title text1
41 Result 90
42 Title text2
42 Result 150 etc.
and I would like to get it to form:
Id key value key2 value2
41 Title text1 Result 90
42 Title text2 Result 150 etc.
Do you have any ideas for this.
This looks simple but do you have any sample data? This information does not exist in the sample data that you provided...
-- Alan Burstein
Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K
. "I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001