Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 15th, 2012  |  syntax: None  |  size: 2.55 KB  |  hits: 15  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Searching all nodes for xml column
  2. <ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  3.   <EntityPropertyOfString>
  4.     <Name>User Label 1</Name>
  5.     <Value>TX 12107210</Value>
  6.   </EntityPropertyOfString>
  7.   <EntityPropertyOfString>
  8.     <Name>User Label 2</Name>
  9.     <Value>BONUS $350/DAY</Value>
  10.   </EntityPropertyOfString>
  11.   <EntityPropertyOfString>
  12.     <Name>User Defined Date 9</Name>
  13.     <Value>11/09/2011</Value>
  14.   </EntityPropertyOfString>
  15. </ArrayOfEntityPropertyOfString>
  16.        
  17. declare @xml xml =
  18. '<ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  19.  <EntityPropertyOfString>
  20.   <Name>User Label 1</Name>
  21.   <Value>TX 12107210</Value>
  22.  </EntityPropertyOfString>
  23. <EntityPropertyOfString>
  24. <Name>User Label 2</Name>
  25. <Value>BONUS $350/DAY</Value>
  26. </EntityPropertyOfString>
  27. <EntityPropertyOfString>
  28. <Name>User Defined Date 9</Name>
  29. <Value>11/09/2011</Value>
  30.  </EntityPropertyOfString>
  31.  </ArrayOfEntityPropertyOfString>'
  32.  
  33. select T.N.value('local-name(.)', 'nvarchar(max)') as NodeName,
  34.        T.N.value('text()[1]', 'nvarchar(max)') as NodeValue
  35. from @xml.nodes('//*') as T(N)
  36. where T.N.value('text()[1]', 'nvarchar(max)') like '%bonus%'
  37.        
  38. NodeName             NodeValue
  39. -------------------- --------------------
  40. Value                BONUS $350/DAY
  41.        
  42. declare @T table (ID int identity primary key, XMLCol xml)
  43.  
  44. declare @xml xml =
  45. '<ArrayOfEntityPropertyOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  46.    <EntityPropertyOfString>
  47.      <Name>User Label 1</Name>
  48.      <Value>TX 12107210</Value>
  49.    </EntityPropertyOfString>
  50.    <EntityPropertyOfString>
  51.      <Name>User Label 2</Name>
  52.      <Value>BONUS $350/DAY</Value>
  53.    </EntityPropertyOfString>
  54.    <EntityPropertyOfString>
  55.      <Name>User Defined Date 9</Name>
  56.      <Value>11/09/2011</Value>
  57.    </EntityPropertyOfString>
  58.  </ArrayOfEntityPropertyOfString>'
  59.  
  60. insert into @T values (@xml)
  61. insert into @T values (@xml)
  62.  
  63. select T1.ID,
  64.        T2.N.value('local-name(.)', 'nvarchar(max)') as NodeName,
  65.        T2.N.value('text()[1]', 'nvarchar(max)') as NodeValue
  66. from @T as T1
  67.   cross apply T1.XMLCol.nodes('//*') as T2(N)  
  68. where T2.N.value('text()[1]', 'nvarchar(max)') like '%bonus%'
  69.        
  70. ID          NodeName             NodeValue
  71. ----------- -------------------- --------------------
  72. 1           Value                BONUS $350/DAY
  73. 2           Value                BONUS $350/DAY
  74.        
  75. SELECT
  76.   ...
  77. FROM
  78.   ...
  79. WHERE COL_NAME LIKE '%bonus%'
create a new version of this paste RAW Paste Data