sql-server - 从SQL Server中的xml字段中选择日期列表

我正在尝试从以下XML中选择日期：

<output xmlns="http://www.abcde.com/pqwlv/dwh" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="46177" xsi:schemaLocation="dwhOutput.xsd">
  <parameter>
    <isCoupon>0</isCoupon>
  </parameter>
  <schedule>
    <executionDate businessDay="2020-01-09">2020-01-09</executionDate>
    <endDates>
      <item businessDay="2019-03-18">2019-03-17</item>
      <item businessDay="2019-06-17">2019-06-17</item>
      <item businessDay="2019-09-17">2019-09-17</item>
      <item businessDay="2019-12-17">2019-12-17</item>
      <item businessDay="2020-03-17">2020-03-17</item>
      <item businessDay="2020-06-17">2020-06-17</item>
    </endDates>
  </schedule>
</output>

我想从“ endDates”字段中选择日期列表（“ businessDay”），因此输出将是（作为表格）：

endDates
----------
2019-03-18
2019-06-17
2019-09-17
2019-12-17
2020-03-17
2020-06-17

我尝试使用此查询：

SELECT endDates.query('.') AS endDates
FROM [table]
CROSS APPLY
OUTPUT.nodes('declare namespace ns="http://www.abcde.com/pqwlv/dwh"; /ns:output/ns:schedule/ns:endDates') AS T1(endDates)

并得到：

<p1:endDates xmlns:p1="http://www.abcde.com/pqwlv/dwh">
  <p1:item businessDay="2019-03-18">2019-03-17</p1:item>
  <p1:item businessDay="2019-06-17">2019-06-17</p1:item>
  <p1:item businessDay="2019-09-17">2019-09-17</p1:item>
  <p1:item businessDay="2019-12-17">2019-12-17</p1:item>
  <p1:item businessDay="2020-03-17">2020-03-17</p1:item>
  <p1:item businessDay="2020-06-17">2020-06-17</p1:item>
</p1:endDates>

如何深入研究并选择日期？

最佳答案

你不远了。您需要在查询开始时使用WITH XMLNAMESPACES来定义名称空间，然后才能遍历FROM中的节点：

DECLARE @xml xml = '<output xmlns="http://www.abcde.com/pqwlv/dwh" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" id="46177" xsi:schemaLocation="dwhOutput.xsd">
  <parameter>
    <isCoupon>0</isCoupon>
  </parameter>
  <schedule>
    <executionDate businessDay="2020-01-09">2020-01-09</executionDate>
    <endDates>
      <item businessDay="2019-03-18">2019-03-17</item>
      <item businessDay="2019-06-17">2019-06-17</item>
      <item businessDay="2019-09-17">2019-09-17</item>
      <item businessDay="2019-12-17">2019-12-17</item>
      <item businessDay="2020-03-17">2020-03-17</item>
      <item businessDay="2020-06-17">2020-06-17</item>
    </endDates>
  </schedule>
</output>';

WITH XMLNAMESPACES(DEFAULT 'http://www.abcde.com/pqwlv/dwh',
                   'http://www.w3.org/2001/XMLSchema-instance' AS xsi)
SELECT eD.item.value('@businessDay','date') AS endDate
FROM (VALUES(@XML))V(YourXML) --This would be your table
     CROSS APPLY V.YourXML.nodes('/output/schedule/endDates/item') eD(item);