Post reply

Reading from XML with a SQL variable

  • December 29, 2010 at 4:29 am

    #229899

    Hi all,

    Firstly merry Christmas to all.

    I've got a problem, where I am trying to read from XML, I'm running the code below and it all works fine.

    FROM @XMLInput.nodes('/data/struct/var[@name="Tier1"]/struct/var[@name="Tier2"]/struct/var') AS i(item)

    However, the node Tier2 can be different, so I need to substitute it with a variable, but I cant seem to figure out how I can acheive this.

    So I want it to look something like;

    FROM @XMLInput.nodes('/data/struct/var[@name="Tier1"]/struct/var[@name="@VariableA"]/struct/var') AS i(item)

    I've done some reading up on 'sql:variable' but I cant seem to get the syntax right, any one any idea's on how I can do this?

    Thanks,

    Nic

  • December 29, 2010 at 5:07 am

    #1267091

    Hi all,

    Managed to figure it out, I basically did the following;

    @XMLInput.nodes('/data/struct/var[@name="Tier1"]/struct/var[@name=sql:variable("@VariableA")]/struct/var')

    Just thought I would share the knowledge incase anyone else has a similar issue.

    Thanks,

    Nic

  • December 29, 2010 at 8:18 am

    #1267205

    Nic,

    Very interesting. Thanks for posting your solution - I've learned something out of this!

    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 3 posts - 1 through 2 (of 2 total)

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