get xml text from varchar column

Question

get xml text from varchar column

Anju Renjith

Ten Centuries

Points: 1135
More actions
January 30, 2015 at 7:14 am

#302675

create table tblxmldata
(id int, xmltext varchar(max))
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText>')
insert into tblxmldata values(1,'<associatedText><value type="PO">Check sugar today please</value></associatedText>')
I want the output as
GTT taken
Check sugar today please

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Anju Renjith (1/30/2015)
create table tblxmldata
(id int, xmltext varchar(max))
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText>')
insert into tblxmldata values(1,'<associatedText><value type="PO">Check sugar today please</value></associatedText>')
I want the output as
GTT taken
Check sugar today please

What have you tried? Why are you storing xml data in a varchar(max) column?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 2

Hi Sean Lange,

It was a mistake. The datatype is xml. Please help

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 3

i tried this

AssociatedText.value('(/associatedText/node())[1]', 'nvarchar(max)') as AssociatedText

If i have more than one value, this wont work. Please help

Lowell SSC Guru Points: 323518 More actions · Answer 4

you need to cross apply any nodes that you expect multiple values from;

something like this example might help.

DECLARE @xml XML

SELECT @xml='<Root>

<Data>

<Item ID="1" AdditionalInfo="Some info">

<SubItem ID="1">apples</SubItem>

<SubItem ID="2">oranges</SubItem>

<SubItem ID="3">cherries</SubItem>

</Item>

<Item ID="2" AdditionalInfo="Some info">

<SubItem ID="1">bananas</SubItem>

<SubItem ID="2">mangos</SubItem>

</Item>

</Data>

</Root>'

SELECT

y.value('@ID[1]','INT') AS SubItemId,

y.value('.','varchar(30)') AS SubItemValue,

c.value('@ID[1]','INT') AS ItemId,

c.value('@AdditionalInfo[1]','varchar(30)') AS AdditionalInfo

FROM @xml.nodes('Root/Data/Item') T(c)

CROSS APPLY

c.nodes('SubItem') X(y)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Assuming xmltext is of type XML

select x.r.value('./text()[1]','nvarchar(max)') as AssociatedText

from tblxmldata

cross apply xmltext.nodes('/associatedText/value') as x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 6

Thank for the quick response

One more help please

Can I get it in a single column as comma separated as

GTT taken,Check sugar today please

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

Try this

select stuff(

(select ','+x.r.value('./text()[1]','nvarchar(max)') as "text()"

from tblxmldata

cross apply xmltext.nodes('/associatedText/value') as x(r)

for xml path('')),1,1,'')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 8

Thanks again

What will be the query if I need the id too in the output

1 GTT taken,Check sugar today please

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 9

This is a bit of a guess...

select t1.id,

stuff((select ','+x.r.value('./text()[1]','nvarchar(max)') as "text()"

from tblxmldata t2

cross apply t2.xmltext.nodes('/associatedText/value') as x(r)

where t2.id = t1.id

for xml path('')),1,1,'')

from tblxmldata t1

group by t1.id

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 10

create table tblxmldata

(id int, xmltext xml)

insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText><associatedText><value type="PO">Check sugar today please</value></associatedText>')

insert into tblxmldata values(2,'<associatedText><value type="PO">Check BP today please</value></associatedText>')

I want the output as

1 GTT taken,Check sugar today please

2 Check BP today please

Thanks for all the previous support

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 11

Have you tried running my last query?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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

I want the output as
GTT taken
Check sugar today please

SELECT id, xmltext.value('(/associatedText/value/text())[1]','varchar(100)')

FROM tblxmldata;

Note that you don't need the nodes() method in this case because you are only retrieving one value from the XML per row.

Can I get it in a single column as comma separated as
GTT taken,Check sugar today please

WITH prep(v) AS

(SELECT

(SELECT xmltext.value('(/associatedText/value/text())[1]','varchar(100)')+', '

FROM tblxmldata x2

WHERE x1.id = x2.id

FOR XML PATH(''))

FROM tblxmldata x1

)

SELECT v = left(v,len(v)-1)

FROM prep

GROUP BY v;

1 GTT taken,Check sugar today please

WITH prep(v) AS

(SELECT CAST(id AS varchar(2)) +' '+

(SELECT xmltext.value('(/associatedText/value/text())[1]','varchar(100)')+', '

FROM tblxmldata x2

WHERE x1.id = x2.id

FOR XML PATH(''))

FROM tblxmldata x1

)

SELECT v = left(v,len(v)-1)

FROM prep

GROUP BY v;

It's important to note that, for performance reasons, you should always include the text() node if you are extracting text. For example:

SELECT xmltext.value('(/associatedText/value)[1]','varchar(100)') ^-------BAD! :ermm:

SELECT xmltext.value('(/associatedText/value/text())[1]','varchar(100)') ^-------GOOD! :w00t:

Edit: Added arrows to clarify what is bad and good.

"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

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

Anju Renjith (2/3/2015)
create table tblxmldata
(id int, xmltext xml)
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText><associatedText><value type="PO">Check sugar today please</value></associatedText>')
insert into tblxmldata values(2,'<associatedText><value type="PO">Check BP today please</value></associatedText>')
I want the output as
1 GTT taken,Check sugar today please
2 Check BP today please
Thanks for all the previous support

WITH prep(id,v) AS

(

SELECT id,

cast(xmltext.query('

for $x in /associatedText

return concat(($x/value/text())[1], ",")') AS varchar(100))

FROM tblxmldata

)

SELECT cast(id as varchar(2))+' '+left(v,len(v)-1)

FROM prep;

"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

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 14

Thanks to all for the support.. esp to Mark Cowne.. You really helped me!