Urgent - How to fetch Drop down values stored in XML Column in SQL 2008

• SG007

  SSC Journeyman

  Points: 78

  More actions

  August 13, 2015 at 1:13 am

  #310889

  Hi All,

  Below is a XML column data. How to get the Id and respective Names for "Case Manager" Dropdown ONLY in SQL server 2008. I don't want to get anything related to "Intake Staff" drop down.

  <DropDown Prompt="Case Manager" Column="case_manager" AddOnly="false" ReadOnly="false" Required="false" CanHaveNotes="no" LabelCssClass="" IndentLevel="1" FirstEntryBlank="false" CssClass="" DefaultValue="" ChoiceType="1">

  <Items>

  <Item Id="1">ABC</Item>

  <Item Id="2">DEF</Item>

  <Item Id="3">GHI</Item>

  <Item Id="4">JKL</Item>

  <Item Id="5">MNO</Item>

  </Items>

  </DropDown>

  <DropDown Prompt="Intake Staff" Column="intake_staff" AddOnly="false" ReadOnly="false" Required="false" CanHaveNotes="no" LabelCssClass="" IndentLevel="1" FirstEntryBlank="false" CssClass="" DefaultValue="" ChoiceType="1">

  <Items>

  <Item Id="1">XYZ</Item>

  <Item Id="2">PQR</Item>

  <Item Id="3">STU</Item> </Items>

  </DropDown>

  Thanks in Advance.

  Cheers!!

• ralu_k_17

  SSCrazy

  Points: 2190

  More actions

  August 14, 2015 at 8:45 am

  #1820080

  Hello,

  You could try to make this one dynamic, in order to not be limited by the number of <Item>

  DECLARE @path varchar(50) = 'c:\sql_server_central\sql1.xml'

  CREATE TABLE Tmp (id INT, instructions XML)

  DECLARE @sqlCommand1 NVARCHAR(2000) = ''

  SET @sqlCommand1 = @sqlCommand1 + 'INSERT INTO Tmp(instructions) ' + CHAR(10) + CHAR(13)

  SET @sqlCommand1 = @sqlCommand1 + 'SELECT CONVERT (XML, BulkColumn, 2 ) FROM OPENROWSET( ' + CHAR(10) + CHAR(13)

  SET @sqlCommand1 = @sqlCommand1 + 'BULK ''' + CAST(@path AS NVARCHAR(255)) + ''','+ CHAR(10) + CHAR(13)

  SET @sqlCommand1 = @sqlCommand1 + 'SINGLE_BLOB ' + CHAR(10) + CHAR(13)

  SET @sqlCommand1 = @sqlCommand1 + ') AS x ' + CHAR(10) + CHAR(13)

  EXEC sp_executesql @sqlCommand1

  SELECT * FROM Tmp

  SET ANSI_NULLS ON

  SET ANSI_PADDING ON

  SET ANSI_WARNINGS ON

  SET ARITHABORT ON

  SET CONCAT_NULL_YIELDS_NULL ON

  SET NUMERIC_ROUNDABORT OFF

  SET QUOTED_IDENTIFIER ON

  SELECT

  instructions.value('(/DropDown/Items/date_acte/text())[1]', 'varchar(40)')

  ,instructions.value('(/DropDown/Items/date_acte/@Id)[1]', 'varchar(40)')

  ,instructions.value('(/DropDown/Items/date_acte/text())[2]', 'varchar(40)')

  ,instructions.value('(/DropDown/Items/date_acte/@Id)[2]', 'varchar(40)')

  FROM Tmp

  WHERE instructions.value('(/DropDown/@Prompt)[1]', 'varchar(40)') = 'Case Manager'

  DROP TABLE Tmp

  SET ANSI_NULLS OFF

  SET ANSI_PADDING OFF

  SET ANSI_WARNINGS OFF

  SET ARITHABORT OFF

  SET CONCAT_NULL_YIELDS_NULL OFF

  SET NUMERIC_ROUNDABORT OFF

  SET QUOTED_IDENTIFIER OFF