XML Field index ??

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  October 15, 2018 at 7:57 am

  #365688

  Hi

  I'm trying to read the data in an XML field with 27 indexes(Sorry if I'm their not called "Index")
  The data is stored with the checked value as true or false and all the true values are stored first
  Im using a case statement to categorize them 
  for that if any index 1 thu 27 = "X" the add 1 to XXX 
  then do it for the other 26  as below
  Case when
  Data.value('(/Data/Type_x0020_of_x0020_Victimization/Table_1/Name)[1]', 'nvarchar(110)') = 'value'
  and Data.value('(/Data/Type_x0020_of_x0020_Victimization/Table_1/Checked)[1]', 'nvarchar(10)') = 'True'
  or
  Data.value('(/Data/Type_x0020_of_x0020_Victimization/Table_1/Name)[2]', 'nvarchar(110)') = 'value'
  and Data.value('(/Data/Type_x0020_of_x0020_Victimization/Table_1/Checked)[2]', 'nvarchar(10)') = 'True'
  or
  Data.value('(/Data/Type_x0020_of_x0020_Victimization/Table_1/Name)[3]', 'nvarchar(110)') = 'value'
  and Data.value('(/Data/Type_x0020_of_x0020_Victimization/Table_1/Checked)[3]', 'nvarchar(10)') = 'True'
  or
  Data.value('(/Data/Type_x0020_of_x0020_Victimization/Table_1/Name)[4]', 'nvarchar(110)') = 'value'
  and Data.value('(/Data/Type_x0020_of_x0020_Victimization/Table_1/Checked)[4]', 'nvarchar(10)') = 'True'

  ETC......................

  then 1 end as XXX

  Is there a better way to do this I tried a wildcard

  Thanks
  Joe

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  October 15, 2018 at 2:44 pm

  #2009746

  Is 'value' the same value for each case of /Data/Type_x0020_of_x0020_Victimization/Table_1? If so, then you can let XPath do some work (along with any XML indexes that apply):

  
-- the function below returns 1 if any of the /Data/Type_x0020_of_x0020_Victimization/Table_1 elements has a /Name element = "value" and a /Check element = "true"
CASE
  WHEN Data.exist('/Data/Type_x0020_of_x0020_Victimization/Table_1[Name="value"][Checked="true"]') = 1 THEN ...

  The XQuery .exist() method is a more efficient search method then extracting XML values with .value() and comparing the results.
  

  Eddie Wuerch
  MCM: SQL

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  October 15, 2018 at 3:02 pm

  #2009756

  Hi Eddie

  Thanks,  that exactly what I needed.

  I just tested it works great is is much faster than the  "Mess" I put together 🙂

  Thanks
  Again

• Eddie Wuerch

  SSChampion

  Points: 12535

  More actions

  October 15, 2018 at 3:07 pm

  #2009758

  Adding...
  If you are comparing node values to a variable, use sql:variable("@VariableName") in the XPath.
  If you are comparing to another column in the query, use sql:column("ColumnName")

  
DECLARE @CheckedState varchar(8) = 'true';
SELECT ...
FROM dbo.MyTable t1
CASE
WHEN Data.exist('/Data/Type_x0020_of_x0020_Victimization/Table_1[Name=sql:column("t1.MyColumn")][Checked=sql:variable("@CheckedState")]') = 1 THEN ...


  Eddie Wuerch
  MCM: SQL

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  October 16, 2018 at 7:25 am

  #2009857

  Ahhh very nice to know!

  Thank You

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  October 16, 2018 at 10:54 am

  #2009916

  Hi Eddie
  Sorry, one more question on this 
  Can I grab the "Name" field if the "Checked" field is 'True'?
  if the field is setup as a for only one choice ?
  so the user gets 3 choice and if 1 is true then [1] name, if 2 is true then [2] name etc...
  without having to do a  case for each ?