get max id in sql with xml

  • 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

  • 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;

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

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