How to read XML through SQL Queries / procedures

• Sankar-403950

  Old Hand

  Points: 310

  More actions

  August 7, 2007 at 8:00 am

  #177975

  Hi All,

  I have XML string stored in database column, by parsing the XML string I need to get some values into variables using SQL Queries / Procedures, Is it possible? Please provide the solution for this.

  Thanks in advance,

  Sankar

   

   

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  August 7, 2007 at 9:51 am

  #725220

  Rather store as XML, not varchar.

  Read BOL! There are plenty examples on how to manipulate XML.

  ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a699d976-8099-4af1-a2f8-cd0e2bd57a83.htm

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

• Joel Ewald

  SSCertifiable

  Points: 6120

  More actions

  August 8, 2007 at 1:36 pm

  #725525

  As Crispin said there are many ways to do this. Here is one.

  CREATE TABLE #AppSettings

  ( SettingCode varchar(4) NOT NULL, SettingValues xml NOT NULL )

  INSERT INTO #AppSettings

  VALUES ( 'SEC', '<SecurityOptions ChangePasswordPromptDays="0" NumberOfLoginAttemptsAllowed="0" />' )

   

  DECLARE @ChangePasswordPromptDays int, @NumberOfLoginAttemptsAllowed int

        --get current value

        SELECT

              @ChangePasswordPromptDays = SettingValues.value('(//SecurityOptions/@ChangePasswordPromptDays)[1]', 'integer'),

              @NumberOfLoginAttemptsAllowed = SettingValues.value('(//SecurityOptions/@NumberOfLoginAttemptsAllowed)[1]', 'integer')

        FROM   #AppSettings

        WHERE  SettingCode = 'SEC'

   

        SELECT @ChangePasswordPromptDays, @NumberOfLoginAttemptsAllowed

       

  DROP TABLE #AppSettings