从 SQL 中的 XML 数据类型中检索特定值

Question

我在SQL中有一些XML数据，如下所示：

<request>
<cookies>
<cookie key="UbiquityEngageReference" expires="May 12, 2030 07:23:57" path="/">L8DnkLHnf0KGhAjXsyKBBQ</cookie>
<cookie key="_gcl_au" expires="Jan 1, 0001" path="/">1.1.1732014746.1581839707</cookie>
<cookie key="_ga" expires="Jan 1, 0001" path="/">GA1.3.1124025076.1581839708</cookie>
<cookie key="_hjid" expires="Jan 1, 0001" path="/">74144e4a-268a-4e14-a2ba-444e48bb3679</cookie>
<cookie key="_fbp" expires="Jan 1, 0001" path="/">fb.2.1581839707827.351945434</cookie>
<cookie key="__qca" expires="Jan 1, 0001" path="/">P0-589291009-1581839707608</cookie>
<cookie key="TS0137ef19" expires="Jan 1, 0001" path="/">012e78320f20ea6dea235cf3523ede45075318433b9648014c3a3dc62c8714e0e1e72c89bcfb332545760b9b4bf2bb93abdeb8dfaf</cookie>
<cookie key="_gid" expires="Jan 1, 0001" path="/">GA1.3.1696252935.1589225007</cookie>
<cookie key="__cfduid" expires="Jan 1, 0001" path="/">dd419d16b619658ab8e88cd08a63823961589225008</cookie>
<cookie key="ARRAffinity" expires="Jan 1, 0001" path="/">b40e06de24c624615a561208952013754b6a7e7409227e1d9fe2bfc009c5ad39</cookie>
<cookie key="_hjIncludedInSample" expires="Jan 1, 0001" path="/">1</cookie>
<cookie key="_dc_gtm_UA-48100938-1" expires="Jan 1, 0001" path="/">1</cookie>
</cookies>
<form>
<value key="dataStoreItemID">y8EAxQVbS0q6_QjX9kVcyg</value>
<value key="ai33_6ohWUWRXSATUzZYOg" />
<value key="mvcOw_Gf6E-jnQjX5dNzAg">16 May 2020</value>
<value key="q9DDmkDBfUqBmAjX5dNzAg">Morning/lunchtime: 6am to 2pm</value>
<value key="1NyHT_kb5EyzhgjX5dNzAg">1</value>
<value key="q5hYoxf0OUq-ugjX5dNzAg">23 May 2020</value>
<value key="gChn521svEyrCgjX5dNzAg">Morning/lunchtime: 6am to 2pm</value>
<value key="4eNnk1BAfEmBHwjX5dNzAg">1</value>
<value key="BI3HO-BQH0Sv5QjX5dNzAg">027 662 4131</value>
<value key="-F8hTIM83UeSXAjX5dNzAg">True</value>
<value key="RNFDdYCE_kaDAQjX5dNzAg">[e-mail]</value>
<value key="_sZQdHuE_kaqjQjX5dNzAg">True</value>
<value key="lTePajhLd0eHagjX5dNzAg">16 May 2020 6:00:00</value>
<value key="SO4dDFuY1kWlkwjX5dNzAg">16 May 2020 13:59:59</value>
<value key="cQo6AI2TVkyI1AjX5dNzAg">23 May 2020 6:00:00</value>
<value key="zs7aM_dlKEKMZwjX5dNzAg">23 May 2020 13:59:59</value>
<value key="XYAFVwMjVUmcMgjX5dNzAg">CfFu5q0817hlLIKeolMzCYOyF7XLOWoqoPkLcgtuYMQ6SSzfwKcGorRwv8VTWfrM</value>
</form>

获取键匹配值"mvcOw_Gf6E-jnQjX5dNzAg"的正确语法是什么？

我有这样的东西，但不正确

Request.value('(request/form/value[@key=mvcOw_Gf6E-jnQjX5dNzAg])[1]','VARCHAR(100)') SHOUT1_FREE_POWER_DATE 

Answer 1

你几乎拥有它，只需在键值周围使用双引号，如果您希望明确获取元素的内容，请调用text()(但这不是必需的(。

select Request.value('(request/form/value[@key="mvcOw_Gf6E-jnQjX5dNzAg"]/text())[1]','varchar(100)') as SHOUT1_FREE_POWER_DATE