Post reply

Extracting XMLdata from Text

  • September 15, 2010 at 9:53 am

    #224067

    I have a table that has XML data stored as text:

    Table : NODEMETADATA

    Column Name: ATTRIBUTES

    I need to extract the Invoice number from the XML data which is contained as below:

    <A35>07466982</A35>

    I am not familiar with using XQUERY and even less when trying to obtain XML stored as text.

    Please, any help grately appreciated

  • September 15, 2010 at 10:25 am

    #1221506

    -- See how this starts off with a table and data in it?

    -- If you had provided us the data in this format,

    -- it would have made things easier for all of the

    -- volunteers on this site to help you out.

    declare @NODEMETADATA TABLE (ATTRIBUTES xml);

    insert into @NODEMETADATA values ('<A35>07466982</A35>');

    select Acct# = n.data.value('.', 'varchar(20)')

    FROM @NODEMETADATA

    CROSS APPLY ATTRIBUTES.nodes('/A35') AS n(data);

    You might want to take a look at the first two links in my signature for better ways to post so that you'll get faster, tested answers.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 2 (of 2 total)

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