Read value of XML attribute in SQL Server 2005

Question

Hello,

I have one table contains field named source with varchar(max).

That field has following value

<OutPatientMedication 
      DateFormat="MM-dd-yyyy" 
      MedicationName="lisinopril 10 mg oral tablet" 
      Instructions="2 cap(s) orally once a day " 
      Status="Active" 
      Quantity="0" 
      Refills="0" 
      PrescriptionType="E">
</OutPatientMedication>

Now I want to fetch value of Instructions attribute.

How can I fetch value?

Prompt reply will be appreciated.

Thanks, Dhruval Shah

Answer 1

Try something like this:

SELECT
    CAST(Source AS XML).value('(/OutPatientMedication/@Instructions)[1]', 'varchar(200)')
FROM
    dbo.YourTable
WHERE
    (condition)

That should give you the desired value.

If you really have only XML in that column, I would strongly recommend making it of type XML in the database! Makes your life a lot easier, and save on disk space, too.