XML Query help

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  January 3, 2018 at 7:51 am

  #342270

  Hi,

  I have below XML and i need to fetch the data for First set of token tag

  
<tokens>
<token>
  <typeid>1</typeid>
  <p1>0.15</p1>
</token>
<token>
  <typeid>1</typeid>
  <op>+</op>
  <p1>2.568</p1>
</token>
</tokens>

  Output i need for first token set as highlighted above Typeid as 1 and p1 as 0.15  i dont want to read/parse the entire xml only first set of tag <token> here in this case. using tsql.  Please help

  Thanks
  Sam

• Thom A

  SSC Guru

  Points: 99126

  More actions

  January 3, 2018 at 8:29 am

  #1974243

  This what you're looking for?

  DECLARE @XML xml;
SET @XML =
'<tokens>
<token>
<typeid>1</typeid>
<p1>0.15</p1>
</token>
<token>
<typeid>1</typeid>
<op>+</op>
<p1>2.568</p1>
</token>
</tokens>';

  SELECT X.T.value('(token/typeid/text())[1]','int') AS typedid,
       X.T.value('(token/p1/text())[1]','decimal(6,3)') AS p1
FROM @XML.nodes('/tokens') X(T);

  

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  January 3, 2018 at 8:44 am

  #1974249

  Since you're only looking for ONE row, there is no reason to use the relatively expensive .nodes() function.

  SELECT @XML.value('(/tokens/token/typeid/text())[1]', 'int') AS typeidid,
    @XML.value('(/tokens/token/p1/text())[1]', 'decimal(6,3)') AS p1id


  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Eirikur Eiriksson

  SSC Guru

  Points: 182879

  More actions

  January 3, 2018 at 8:54 am

  #1974253

  drew.allen - Wednesday, January 3, 2018 8:44 AM

  Since you're only looking for ONE row, there is no reason to use the relatively expensive .nodes() function.

  SELECT @XML.value('(/tokens/token/typeid/text())[1]', 'int') AS typeidid,
    @XML.value('(/tokens/token/p1/text())[1]', 'decimal(6,3)') AS p1id


  Drew

  Good point Drew, the nodes() function will parse and then reconstruct the xml, quite costly!
  😎

• Thom A

  SSC Guru

  Points: 99126

  More actions

  January 3, 2018 at 9:14 am

  #1974265

  Eirikur Eiriksson - Wednesday, January 3, 2018 8:54 AM

  drew.allen - Wednesday, January 3, 2018 8:44 AM

  Since you're only looking for ONE row, there is no reason to use the relatively expensive .nodes() function.

  SELECT @XML.value('(/tokens/token/typeid/text())[1]', 'int') AS typeidid,
    @XML.value('(/tokens/token/p1/text())[1]', 'decimal(6,3)') AS p1id


  Drew

  Good point Drew, the nodes() function will parse and then reconstruct the xml, quite costly!
  😎

  Hadn't considered that, in all honestly. Thanks Drew. 🙂

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sam 55243

  SSCrazy

  Points: 2697

  More actions

  January 3, 2018 at 10:25 am

  #1974281

  Thom A - Wednesday, January 3, 2018 9:14 AM

  Eirikur Eiriksson - Wednesday, January 3, 2018 8:54 AM

  drew.allen - Wednesday, January 3, 2018 8:44 AM

  Since you're only looking for ONE row, there is no reason to use the relatively expensive .nodes() function.

  SELECT @XML.value('(/tokens/token/typeid/text())[1]', 'int') AS typeidid,
    @XML.value('(/tokens/token/p1/text())[1]', 'decimal(6,3)') AS p1id


  Drew

  Good point Drew, the nodes() function will parse and then reconstruct the xml, quite costly!
  😎

  Hadn't considered that, in all honestly. Thanks Drew. 🙂

  Thanks for Quick help..