Home Forums SQL Server 2008 T-SQL (SS2K8) Trying to select XML data for one tag from an text field and isn't returning the data I need RE: Trying to select XML data for one tag from an text field and isn't returning the data I need

  • February 12, 2013 at 5:33 am

    #1586954

    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!