Extracting XML

Question

Post reply

Extracting XML

timvil

Grasshopper

Points: 14
More actions
November 9, 2013 at 5:26 am

#75683

I know this is not a new issue but:
How can I extract data from XML column:
I have DB1 and Table1 and Column1. In Column1 I have XML data: <instance><Title>New product</Title> <Date> 2013.10.24</Date><Author>TV</Author><Meter>LV</Meter><Result>90</ Result ></instance>
Now I want to query two values from that column: <Title> and <Result> to make an diagram.
How can I do this?
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

declare @Table1 table(Column1 xml);

insert @Table1(Column1)

values( '<instance><Title>New product</Title> <Date> 2013.10.24</Date><Author>TV</Author><Meter>LV</Meter><Result>90</Result ></instance>');

select

Column1.value('(instance/Title/text())[1]','varchar(255)') as title

, Column1.value('(instance/Result/text())[1]','varchar(255)') as result

from @Table1

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

timvil Grasshopper Points: 14 More actions · Answer 2

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?

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)

Idkeyvalue

41Titletext1

41Result90

42Titletext2

42Result150etc.

and I would like to get it to form:

Idkeyvaluekey2value2

41Titletext1Result90

42Titletext2Result150 etc.

Do you have any ideas for this.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

timvil (11/11/2013)
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);

insert @Table1(Column1)

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)

select

Column1.value('(instance/Title/text())[1]','varchar(255)') as title

, Column1.value('(instance/Result/text())[1]','varchar(255)') as result

from ntext_to_xml

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)
Idkeyvalue
41Titletext1
41Result90
42Titletext2
42Result150etc.
and I would like to get it to form:
Idkeyvaluekey2value2
41Titletext1Result90
42Titletext2Result150 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...

"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