Trying to select XML data for one tag from an text field and isn't returning the data I need

Question

Trying to select XML data for one tag from an text field and isn't returning the data I need

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
February 11, 2013 at 5:25 pm

#289258

Hello DBA's/Developers,
I'm trying to select XML data from one tag that is stored in an TEXT data type and can't seem to return the data I need.
e.g. XMLProfile_TXT field and tag </DID>.
When I try to use the following TSQL syntax below, I get '0' for every record returned:
select charindex ('</DID>', XMLProfile_TXT) from table_name
Everytime I select all the XML data I can see the data I need in the result set, but I don't want every tag, I just need the one noted above (e.g. </DID>).
Can someone help? I'm sure this is an easy answer. 🙂
Thanks!
-Dave

Viewing 8 posts - 1 through 7 (of 7 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

davidsalazar01 (2/11/2013)
Hello DBA's/Developers,
I'm trying to select XML data from one tag that is stored in an TEXT data type and can't seem to return the data I need.
e.g. XMLProfile_TXT field and tag </DID>.
When I try to use the following TSQL syntax below, I get '0' for every record returned:
select charindex ('</DID>', XMLProfile_TXT) from table_name
Everytime I select all the XML data I can see the data I need in the result set, but I don't want every tag, I just need the one noted above (e.g. </DID>).
Can someone help? I'm sure this is an easy answer. 🙂
Thanks!
-Dave

Hello and welcome to SSC,

If you could knock up some sample data and DDL scripts, then your expected results based on the sample data provided that would be extremely useful in allowing people to help you.

Have a read through this link --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D, if you're unsure how best to lay this information out. Remember that if your sample data and DDL script is readily consumable, then the volunteers for this forum are much more likely to take time out from their work to give you a hand.

Thanks.

--EDIT--

This thread (http://www.sqlservercentral.com/Forums/Topic1415340-338-1.aspx) might help.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323504 More actions · Answer 2

david the select statement you posted will find where the END tag begins...wouldn't you want the value between the <did> </did> tags?

could the opening <did tag have attributes?

is there more than one <did tag in the xml?

this is how i would do it, using the function DelimitedSplit8K(search SSC for the article)

With table_name (ID,XMLProfile_TXT)

AS

(SELECT 1,'

<div class="MainMenu"

><ul>

<li><did>Home</did></li>

<li><did>Tags</did></li>

<li><did>Articles</did></li>

<li><did>Editorials</did></li>

<li><>Stairways</did></li>

<li><did>Forums</did></li>

<li><did>Scripts</did></li>

<li><did>Videos</did></li>

<li><did>Blogs</did></li>

<li><did>QotD</did></li>

<li><did class="track-clicks MenuItem "href="/Books/"data-event-category="MainMenu: Books">Books</did></li>

<li><did class="track-clicks MenuItem "href="http://ask.sqlservercentral.com"data-event-category="MainMenu: Ask SSC">Ask SSC</did></li>

<li><did class="track-clicks MenuItem "href="http://jobs.sqlservercentral.com/"data-event-category="MainMenu: SQL Jobs">SQL Jobs</did></li>

<li><did class="track-clicks MenuItem "href="/Training/"data-event-category="MainMenu: Training">Training</did></li>

<li><did class="track-clicks MenuItem "href="/Authors/Articles/"data-event-category="MainMenu: Authors">Authors</did></li>

<li><did class="track-clicks MenuItem Minor"href="ssc_showPostsSinceLastVisit()"data-event-category="MainMenu: Active Threads">Active Threads</did></li>

<li><did class="track-clicks MenuItem Minor"href="/About/AboutUs/"data-event-category="MainMenu: About us">About us</did></li>

<li><did class="track-clicks MenuItem Minor"href="/About/ContactUs/"data-event-category="MainMenu: Contact us">Contact us</did></li>

<li><did class="track-clicks MenuItem Minor"href="/NewsletterArchive"data-event-category="MainMenu: Newsletters">Newsletters</did></li>

<li><did class="track-clicks MenuItem Minor"href="/Contributions/Home"data-event-category="MainMenu: Write for us">Write for us</did></li>

</ul>

</div>' UNION ALL

SELECT 2, '<li><did>Home</did></li>

<li><did>Tags</did></li>

<li><did>Articles</did></li>

<li><did>Editorials</did></li>

<li><>Stairways</did></li>

<li><did>Forums</did></li>

<li><did>Scripts</did></li>

<li><did>Videos</did></li>

<li><did>Blogs</did></li>

<li><did>QotD</did></li>

<li><did class="track-c'

)

select

myfn.*,

table_name.*,

REPLACE(myfn.Item,'</did','')

from table_name

CROSS APPLY dbo.DelimitedSplit8K(XMLProfile_TXT,'>') myfn

Where myfn.Item LIKE '%</did'

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!

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 3

Lowell,

It's only one occurence (e.g.<DID>) in the XML and your right I only want the data between the tags. Would the samples you posted do the trick? I appreciate your help!

Thanks,

David

Lowell SSC Guru Points: 323504 More actions · Answer 4

davidsalazar01 (2/12/2013)
Lowell,
It's only one occurence (e.g.<DID>) in the XML and your right I only want the data between the tags. Would the samples you posted do the trick? I appreciate your help!
Thanks,
David

yes it would do it, and is prepared for the other things i mentioned as well: multiple DID tags, and attributes in the opening <did tag as well.

grab the function from this article, and read the article so you can understand what it does; it's a very powerful tool to put in your toolkit.

Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]

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!

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

From the sample data provided by Lowell: -

-- SAMPLE DATA

IF object_id('tempdb..#yourTable') IS NOT NULL

BEGIN

DROP TABLE #yourTable;

END;

SELECT id, CAST(XMLProfile_TXT AS XML) AS XMLProfile_TXT

INTO #yourTable

FROM (VALUES(1, '<div class="MainMenu"><ul><li><did>Home</did></li><li><did>Tags</did></li><li><did>Articles</did></li><li><did>Editorials</did></li><li><did>Stairways</did></li>

<li><did>Forums</did></li><li><did>Scripts</did></li><li><did>Videos</did></li><li><did>Blogs</did></li><li><did>QotD</did></li>