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 -- Jeff Moden
How to Post Performance Problems -- Gail Shaw
Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K
I cant 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. -- Itzik Ben-Gan 2001