Dynamically retrieve the data from XML

  • Hello,

    I am using SQL Server 2008.

    I want to retrieve the data from XML dynamically.

    For example,

    declare @XMLdata XML

    SET @XMLdata =

    '<XmlStart>

    <data>

    <Col1>

    <col1Data>

    <data1>5</data1>

    <data2>

    <data3>Hello</data3>

    </data2>

    <data4>

    <data3>Hello1</data3>

    </data4>

    </col1Data>

    </Col1>

    </data>

    </XmlStart>'

    select T.C.value('(data2/data3)[1]', 'varchar(10)') AS Column1

    FROM @XMLdata.nodes('XmlStart/data/Col1/col1Data') T(C)

    select T.C.value('(data4/data3)[1]', 'varchar(10)') AS Column1

    FROM @XMLdata.nodes('XmlStart/data/Col1/col1Data') T(C)

    In the above code, I want to use variable to select the tag dynamically.

    For example,

    declare @aa varchar(10)

    set @aa = 'data4'

    select T.C.value('('+@aa+'/data3)[1]', 'varchar(10)') AS Column1

    FROM @XMLdata.nodes('XmlStart/data/Col1/col1Data') T(C)

    But, sql server throws an error like:

    Msg 8172, Level 16, State 1, Line 28

    The argument 1 of the XML data type method "value" must be a string literal.

    Please guide me how can I solve this issue.

    Thanks

  • I think that in this case you’ll have to use dynamic SQL. Notice that the dynamic SQL won’t recognize the variable that holds the data, so you’ll have to use a temporary table. Also dynamic SQL is problematic and might expose you to SQL Injection. Unfortunately I can’t think of a way to do what you want without dynamic SQL. Maybe someone else will come up with another way to do it. Here is a small script that shows how to do it with dynamic SQL:

    declare @XMLdata XML

    SET @XMLdata =

    '<XmlStart>

    <data>

    <Col1>

    <col1Data>

    <data1>5</data1>

    <data2>

    <data3>Hello</data3>

    </data2>

    <data4>

    <data3>Hello1</data3>

    </data4>

    </col1Data>

    </Col1>

    </data>

    </XmlStart>'

    declare @aa varchar(10)

    declare @Xquery nvarchar(500)

    set @aa = 'data4'

    create table #xml (x xml)

    insert into #xml (x)

    values (@XMLdata)

    set @Xquery = 'select T.C.value(''(' + @aa + '/data3)[1]'', ''varchar(10)'') AS Column1

    from #xml cross apply x.nodes(''XmlStart/data/Col1/col1Data'') T(C)'

    exec sp_executesql @Xquery

    drop table #xml

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • You don't have to use dynamic SQL. However, if there are many nodes under col1Data you should compare the performance of the following solution to the dynamic SQL version... Thinking about it: you should compare the performance anyway 😀

    DECLARE @aa VARCHAR(10)

    SET @aa = 'data4'

    SELECT

    y.value('(.)', 'VARCHAR(50)') AS Column1

    FROM @XMLdata.nodes('XmlStart/data/Col1/col1Data') t(c)

    CROSS APPLY

    t.c.nodes('*') x(y)

    WHERE y.value('local-name(.)', 'VARCHAR(50)') = @aa



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

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

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