get max id in sql with xml

• kiasystemsoft

  SSC Veteran

  Points: 249

  More actions

  June 2, 2014 at 11:21 pm

  #292012

  hi

  i have a xml file for save records in table

  problem:

  i have a field id

  i can not get max this field for save in xml

  i Do not want use identity

• SpeedySQL

  Ten Centuries

  Points: 1019

  More actions

  June 3, 2014 at 6:29 am

  #1718495

  Try the following:

  --Using a table variable.

  DECLARE @data table(col1 XML);

  INSERT @data

  SELECT

  '<data>

  <test>

  <ID>1</ID>

  <Value>100</Value>

  </test>

  <test>

  <ID>2</ID>

  <Value>200</Value>

  </test>

  <test>

  <ID>3</ID>

  <Value>300</Value>

  </test>

  </data>';

  WITH Result

  AS

  (SELECT a.col.value('ID[1]','int') AS ID,

  a.col.value('Value[1]', 'int') AS Value

  FROM @data

  CROSS APPLY col1.nodes('//data/test') a(col)

  )

  SELECT MAX(ID) MAX_ID

  FROM Result;

  SpeedySQL