Dynamically retrieve the data from XML

• Hardy21

  SSCrazy Eights

  Points: 9708

  More actions

  April 14, 2010 at 12:49 am

  #96653

  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

• Adi Cohn-120898

  SSC-Dedicated

  Points: 33944

  More actions

  April 14, 2010 at 1:33 am

  #1151149

  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/

• LutzM

  SSC Guru

  Points: 107049

  More actions

  April 14, 2010 at 12:09 pm

  #1151613

  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]