时间戳

首页 > 解决方案 > 如何使用 CROSS APPLY 简化此 XML 代码

sql-server - 如何使用 CROSS APPLY 简化此 XML 代码

问题描述

我是 XML 操作的新手，请看下面的代码。

我有如下代码

注意：我只是通过添加 @m= '2',
@m= '3',@m= '4',@m= '5', ...... ., @m='13'。在每个连续的代码块中。

有什么方法可以使用 CROSS APPLY 或任何其他简化方式来简化此代码？

这是该表的架构，其中包含 db<>fiddle 中的实际数据：这里

 SELECT 
RECID as Branch,
XMLRECORD.value('(/row/c2/text())[1]', 'nvarchar(10)') as UpdateDate,
XMLRECORD.value('(/row/c3[@m="2"]/text())[1]', 'nvarchar(10)') as Currency,
XMLRECORD.value('(/row/c6[@m="2"]/text())[1]', 'nvarchar(10)') as [MidRevealRate_CCY_Market-CASH],
XMLRECORD.value('(/row/c8[@m="2"]/text())[1]', 'nvarchar(10)') as [BuyRate_CCY_Market-IFT],
XMLRECORD.value('(/row/c8[@m="2"][@s="2"]/text())[1]', 'nvarchar(10)') as [BuyRate_CCY_Market-CASH],
XMLRECORD.value('(/row/c9[@m="2"]/text())[1]', 'nvarchar(10)') as [SellRate_CCY_Market-CASH]
FROM [FBNK_PT_CURRENCY]
WHERE RECID in ('VU0010002')

UNION

SELECT 
RECID as Branch,
XMLRECORD.value('(/row/c2/text())[1]', 'nvarchar(10)') as UpdateDate,
XMLRECORD.value('(/row/c3[@m="3"]/text())[1]', 'nvarchar(10)') as Currency,
XMLRECORD.value('(/row/c6[@m="3"]/text())[1]', 'nvarchar(10)') as [MidRevealRate_CCY_Market-CASH],
XMLRECORD.value('(/row/c8[@m="3"]/text())[1]', 'nvarchar(10)') as [BuyRate_CCY_Market-IFT],
XMLRECORD.value('(/row/c8[@m="3"][@s="2"]/text())[1]', 'nvarchar(10)') as [BuyRate_CCY_Market-CASH],
XMLRECORD.value('(/row/c9[@m="3"]/text())[1]', 'nvarchar(10)') as [SellRate_CCY_Market-CASH]
FROM [FBNK_PT_CURRENCY]
WHERE RECID in ('VU0010002')

UNION

SELECT 
RECID as Branch,
XMLRECORD.value('(/row/c2/text())[1]', 'nvarchar(10)') as UpdateDate,
XMLRECORD.value('(/row/c3[@m="4"]/text())[1]', 'nvarchar(10)') as Currency,
XMLRECORD.value('(/row/c6[@m="4"]/text())[1]', 'nvarchar(10)') as [MidRevealRate_CCY_Market-CASH],
XMLRECORD.value('(/row/c8[@m="4"]/text())[1]', 'nvarchar(10)') as [BuyRate_CCY_Market-IFT],
XMLRECORD.value('(/row/c8[@m="4"][@s="2"]/text())[1]', 'nvarchar(10)') as [BuyRate_CCY_Market-CASH],
XMLRECORD.value('(/row/c9[@m="4"]/text())[1]', 'nvarchar(10)') as [SellRate_CCY_Market-CASH]
FROM [FBNK_PT_CURRENCY]
WHERE RECID in ('VU0010002')

有人可以提供一种方法来支持此代码吗？提前致谢！

标签： sql-serverxmltsqlsql-server-2008xml-parsing

解决方案

抱歉，这个 XML 的结构真的很糟糕……在我完整回答你的问题之前，你必须帮助我了解背后的业务逻辑。

您的 XML 看起来像这样

<row id="VU0010002" xml:space="preserve">
  <c2>20181210</c2>
  <c3>AUD</c3>
  <c3 m="2">EUR</c3>
  <c3 m="3">FJD</c3>
  <c3 m="4">GBP</c3>
  <c3 m="5">HKD</c3>
  <c3 m="6">JPY</c3>
  <c3 m="7">NZD</c3>
  <c3 m="8">PGK</c3>
  <c3 m="9">SBD</c3>
  <c3 m="10">SGD</c3>
  <c3 m="11">USD</c3>
  <c3 m="12">VUV</c3>
  <c3 m="13">XPF</c3>
  <c4>1</c4>
  <c4 m="1" s="2">10</c4>
  <c4 m="2">1</c4>
  <c4 m="2" s="2">10</c4>
  <c4 m="3">1</c4>
  <c4 m="3" s="2">10</c4>
  <c4 m="4">1</c4>
  <c4 m="4" s="2">10</c4>
  <c4 m="5">1</c4>
  <c4 m="5" s="2">10</c4>
  <c4 m="6">1</c4>
  <c4 m="6" s="2">10</c4>
  <c4 m="7">1</c4>
  <c4 m="7" s="2">10</c4>
  <c4 m="8">1</c4>
  <c4 m="8" s="2">10</c4>
  <c4 m="9">1</c4>
  <c4 m="9" s="2">10</c4>
  <c4 m="10">1</c4>
  <c4 m="10" s="2">10</c4>
  <c4 m="11">1</c4>
  <c4 m="11" s="2">10</c4>
  <c4 m="12">1</c4>
  <c4 m="12" s="2">10</c4>
  <c4 m="13">1</c4>
  <c4 m="13" s="2">10</c4>
  <c5 m="1" s="2" />
  <c5 m="2" s="2" />
  <c5 m="3" s="2" />
  <c5 m="4" s="2" />
  <c5 m="5" s="2" />
  <c5 m="6" s="2" />
  <c5 m="7" s="2" />
  <c5 m="8" s="2" />
  <c5 m="9" s="2" />
  <c5 m="10" s="2" />
  <c5 m="11" s="2" />
  <c5 m="12" s="2" />
  <c5 m="13" s="2" />
  <c6>80.98</c6>
  <c6 m="1" s="2">80.98</c6>
  <c6 m="2">128.39</c6>
  <c6 m="2" s="2">128.39</c6>
  <c6 m="3">53.22</c6>
  <c6 m="3" s="2">53.22</c6>
  <c6 m="4">143.07</c6>
  <c6 m="4" s="2">143.07</c6>
  <c6 m="5">14.46</c6>
  <c6 m="5" s="2">14.46</c6>
  <c6 m="6">1.0008</c6>
  <c6 m="6" s="2">1.0008</c6>
  <c6 m="7">77.26</c6>
  <c6 m="7" s="2">77.26</c6>
  <c6 m="8">34.34</c6>
  <c6 m="8" s="2">34.34</c6>
  <c6 m="9">14.64</c6>
  <c6 m="9" s="2">14.64</c6>
  <c6 m="10">82.41</c6>
  <c6 m="10" s="2">82.41</c6>
  <c6 m="11">112.6</c6>
  <c6 m="11" s="2">112.6</c6>
  <c6 m="12" s="2" />
  <c6 m="13">1.0746</c6>
  <c6 m="13" s="2">1.0746</c6>
  <c7>0</c7>
  <c7 m="1" s="2">0</c7>
  <c7 m="2">0</c7>
  <c7 m="2" s="2">0</c7>
  <c7 m="3">0</c7>
  <c7 m="3" s="2">0</c7>
  <c7 m="4">0</c7>
  <c7 m="4" s="2">0</c7>
  <c7 m="5">0</c7>
  <c7 m="5" s="2">0</c7>
  <c7 m="6">0</c7>
  <c7 m="6" s="2">0</c7>
  <c7 m="7">0</c7>
  <c7 m="7" s="2">0</c7>
  <c7 m="8">0</c7>
  <c7 m="8" s="2">0</c7>
  <c7 m="9">0</c7>
  <c7 m="9" s="2">0</c7>
  <c7 m="10">0</c7>
  <c7 m="10" s="2">0</c7>
  <c7 m="11">0</c7>
  <c7 m="11" s="2">0</c7>
  <c7 m="12" s="2" />
  <c7 m="13">0</c7>
  <c7 m="13" s="2">0</c7>
  <c8>80.08</c8>
  <c8 m="1" s="2">79.39</c8>
  <c8 m="2">127.17</c8>
  <c8 m="2" s="2">119.52</c8>
  <c8 m="3">51.58</c8>
  <c8 m="3" s="2">49.99</c8>
  <c8 m="4">141.73</c8>
  <c8 m="4" s="2">134.44</c8>
  <c8 m="5">13.79</c8>
  <c8 m="5" s="2">11.12</c8>
  <c8 m="6">0.9915</c8>
  <c8 m="6" s="2">0.8796</c8>
  <c8 m="7">76.58</c8>
  <c8 m="7" s="2">74.19</c8>
  <c8 m="8">32.25</c8>
  <c8 m="8" s="2">25.71</c8>
  <c8 m="9">13.97</c8>
  <c8 m="9" s="2">13.97</c8>
  <c8 m="10">81.42</c8>
  <c8 m="10" s="2">64.46</c8>
  <c8 m="11">111.43</c8>
  <c8 m="11" s="2">109.05</c8>
  <c8 m="12" s="2" />
  <c8 m="13">1.0646</c8>
  <c8 m="13" s="2">0.938</c8>
  <c9>84.37</c9>
  <c9 m="1" s="2">84.37</c9>
  <c9 m="2">132.17</c9>
  <c9 m="2" s="2">132.17</c9>
  <c9 m="3">55.57</c9>
  <c9 m="3" s="2">55.57</c9>
  <c9 m="4">146.31</c9>
  <c9 m="4" s="2">146.31</c9>
  <c9 m="5">15.16</c9>
  <c9 m="5" s="2">15.16</c9>
  <c9 m="6">1.038</c9>
  <c9 m="6" s="2">1.038</c9>
  <c9 m="7">80.89</c9>
  <c9 m="7" s="2">80.89</c9>
  <c9 m="8">35.85</c9>
  <c9 m="8" s="2">35.85</c9>
  <c9 m="9">15.84</c9>
  <c9 m="9" s="2">15.84</c9>
  <c9 m="10">85.2</c9>
  <c9 m="10" s="2">85.2</c9>
  <c9 m="11">117.6</c9>
  <c9 m="11" s="2">117.6</c9>
  <c9 m="12" s="2" />
  <c9 m="13">1.1119</c9>
  <c9 m="13" s="2">1.1119</c9>
  <c10 m="13" />
  <c11 m="13" />
  <c12 m="13" />
  <c13>100000.00</c13>
  <c13 m="2">100000.00</c13>
  <c13 m="13" />
  <c14 m="13" />
  <c15 m="13" />
  <c16 m="13" />
  <c18>795</c18>
  <c19>102_JWABAIAT.1__OFS_BROWSERTC</c19>
  <c20>1812101134</c20>
  <c21>11_GARUKESA.1_OFS_BROWSERTC</c21>
  <c22>VU0010001</c22>
  <c23>8211</c23>
</row>

这种XQuery / FLWOR方法会将其转换为 - 希望是正确的结构：（
我的假设：没有属性的元素m以某种方式开始一个部分）

SELECT XMLRECORD.query
    ('
        <row>
        {
        for $nd in /row/*[empty(@m)]
        return <leadNode name="{local-name($nd)}" value="{$nd/text()}">
        <below>
        {
            for $below in /row/*[local-name()=local-name($nd) and .!=$nd]
            return  <detail>{$below/@*}{$below/text()}</detail>
        }
        </below>        
        </leadNode>
        }
        </row>
    ')
FROM FBNK_PT_CURRENCY;

结果

<row>
  <leadNode name="c2" value="20181210">
    <below />
  </leadNode>
  <leadNode name="c3" value="AUD">
    <below>
      <detail m="2">EUR</detail>
      <detail m="3">FJD</detail>
      <detail m="4">GBP</detail>
      <detail m="5">HKD</detail>
      <detail m="6">JPY</detail>
      <detail m="7">NZD</detail>
      <detail m="8">PGK</detail>
      <detail m="9">SBD</detail>
      <detail m="10">SGD</detail>
      <detail m="11">USD</detail>
      <detail m="12">VUV</detail>
      <detail m="13">XPF</detail>
    </below>
  </leadNode>
  <leadNode name="c4" value="1">
    <below>
      <detail m="1" s="2">10</detail>
      <detail m="2" s="2">10</detail>
      <detail m="3" s="2">10</detail>
      <detail m="4" s="2">10</detail>
      <detail m="5" s="2">10</detail>
      <detail m="6" s="2">10</detail>
      <detail m="7" s="2">10</detail>
      <detail m="8" s="2">10</detail>
      <detail m="9" s="2">10</detail>
      <detail m="10" s="2">10</detail>
      <detail m="11" s="2">10</detail>
      <detail m="12" s="2">10</detail>
      <detail m="13" s="2">10</detail>
    </below>
  </leadNode>
  <leadNode name="c6" value="80.98">
    <below>
      <detail m="2">128.39</detail>
      <detail m="2" s="2">128.39</detail>
      <detail m="3">53.22</detail>
      <detail m="3" s="2">53.22</detail>
      <detail m="4">143.07</detail>
      <detail m="4" s="2">143.07</detail>
      <detail m="5">14.46</detail>
      <detail m="5" s="2">14.46</detail>
      <detail m="6">1.0008</detail>
      <detail m="6" s="2">1.0008</detail>
      <detail m="7">77.26</detail>
      <detail m="7" s="2">77.26</detail>
      <detail m="8">34.34</detail>
      <detail m="8" s="2">34.34</detail>
      <detail m="9">14.64</detail>
      <detail m="9" s="2">14.64</detail>
      <detail m="10">82.41</detail>
      <detail m="10" s="2">82.41</detail>
      <detail m="11">112.6</detail>
      <detail m="11" s="2">112.6</detail>
      <detail m="12" s="2" />
      <detail m="13">1.0746</detail>
      <detail m="13" s="2">1.0746</detail>
    </below>
  </leadNode>
  <leadNode name="c7" value="0">
    <below>
      <detail m="12" s="2" />
    </below>
  </leadNode>
  <leadNode name="c8" value="80.08">
    <below>
      <detail m="1" s="2">79.39</detail>
      <detail m="2">127.17</detail>
      <detail m="2" s="2">119.52</detail>
      <detail m="3">51.58</detail>
      <detail m="3" s="2">49.99</detail>
      <detail m="4">141.73</detail>
      <detail m="4" s="2">134.44</detail>
      <detail m="5">13.79</detail>
      <detail m="5" s="2">11.12</detail>
      <detail m="6">0.9915</detail>
      <detail m="6" s="2">0.8796</detail>
      <detail m="7">76.58</detail>
      <detail m="7" s="2">74.19</detail>
      <detail m="8">32.25</detail>
      <detail m="8" s="2">25.71</detail>
      <detail m="9">13.97</detail>
      <detail m="9" s="2">13.97</detail>
      <detail m="10">81.42</detail>
      <detail m="10" s="2">64.46</detail>
      <detail m="11">111.43</detail>
      <detail m="11" s="2">109.05</detail>
      <detail m="12" s="2" />
      <detail m="13">1.0646</detail>
      <detail m="13" s="2">0.938</detail>
    </below>
  </leadNode>
  <leadNode name="c9" value="84.37">
    <below>
      <detail m="2">132.17</detail>
      <detail m="2" s="2">132.17</detail>
      <detail m="3">55.57</detail>
      <detail m="3" s="2">55.57</detail>
      <detail m="4">146.31</detail>
      <detail m="4" s="2">146.31</detail>
      <detail m="5">15.16</detail>
      <detail m="5" s="2">15.16</detail>
      <detail m="6">1.038</detail>
      <detail m="6" s="2">1.038</detail>
      <detail m="7">80.89</detail>
      <detail m="7" s="2">80.89</detail>
      <detail m="8">35.85</detail>
      <detail m="8" s="2">35.85</detail>
      <detail m="9">15.84</detail>
      <detail m="9" s="2">15.84</detail>
      <detail m="10">85.2</detail>
      <detail m="10" s="2">85.2</detail>
      <detail m="11">117.6</detail>
      <detail m="11" s="2">117.6</detail>
      <detail m="12" s="2" />
      <detail m="13">1.1119</detail>
      <detail m="13" s="2">1.1119</detail>
    </below>
  </leadNode>
  <leadNode name="c13" value="100000.00">
    <below>
      <detail m="13" />
    </below>
  </leadNode>
  <leadNode name="c18" value="795">
    <below />
  </leadNode>
  <leadNode name="c19" value="102_JWABAIAT.1__OFS_BROWSERTC">
    <below />
  </leadNode>
  <leadNode name="c20" value="1812101134">
    <below />
  </leadNode>
  <leadNode name="c21" value="11_GARUKESA.1_OFS_BROWSERTC">
    <below />
  </leadNode>
  <leadNode name="c22" value="VU0010001">
    <below />
  </leadNode>
  <leadNode name="c23" value="8211">
    <below />
  </leadNode>
</row>

像这样的查询

;WITH Transformed(TheXml) AS
(
    SELECT XMLRECORD.query
        ('
            <row>
            {
            for $nd in /row/*[empty(@m)]
            return <leadNode name="{local-name($nd)}" value="{$nd/text()}">
            <below>
            {
                for $below in /row/*[local-name()=local-name($nd) and .!=$nd]
                return  <detail>{$below/@*}{$below/text()}</detail>
            }
            </below>        
            </leadNode>
            }
            </row>
        ')
    FROM FBNK_PT_CURRENCY
)
SELECT ln.value('@name','nvarchar(max)') AS LeadNode_Name
      ,ln.value('@value','nvarchar(max)') AS LeadNode_Value
      ,det.value('@m','int') AS Detail_M
      ,det.value('@s','int') AS Detail_S
      ,det.value('text()[1]','nvarchar(max)') AS Detail_Value
FROM Transformed
OUTER APPLY TheXml.nodes('/row/leadNode') A(ln)
OUTER APPLY ln.nodes('below/detail') B(det);

...你会得到全部和一切（由于限制为 30.000 个字符，不得不删掉它）：

+---------------+-------------------------------+----------+----------+--------------+
| LeadNode_Name | LeadNode_Value                | Detail_M | Detail_S | Detail_Value |
+---------------+-------------------------------+----------+----------+--------------+
| c2            | 20181210                      | NULL     | NULL     | NULL         |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 2        | NULL     | EUR          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 3        | NULL     | FJD          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 4        | NULL     | GBP          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 5        | NULL     | HKD          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 6        | NULL     | JPY          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 7        | NULL     | NZD          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 8        | NULL     | PGK          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 9        | NULL     | SBD          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 10       | NULL     | SGD          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 11       | NULL     | USD          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 12       | NULL     | VUV          |
+---------------+-------------------------------+----------+----------+--------------+
| c3            | AUD                           | 13       | NULL     | XPF          |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 1        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 2        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 3        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 4        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 5        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 6        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 7        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 8        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 9        | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 10       | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 11       | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 12       | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c4            | 1                             | 13       | 2        | 10           |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 2        | NULL     | 128.39       |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 2        | 2        | 128.39       |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 3        | NULL     | 53.22        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 3        | 2        | 53.22        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 4        | NULL     | 143.07       |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 4        | 2        | 143.07       |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 5        | NULL     | 14.46        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 5        | 2        | 14.46        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 6        | NULL     | 1.0008       |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 6        | 2        | 1.0008       |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 7        | NULL     | 77.26        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 7        | 2        | 77.26        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 8        | NULL     | 34.34        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 8        | 2        | 34.34        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 9        | NULL     | 14.64        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 9        | 2        | 14.64        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 10       | NULL     | 82.41        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 10       | 2        | 82.41        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 11       | NULL     | 112.6        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 11       | 2        | 112.6        |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 12       | 2        | NULL         |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 13       | NULL     | 1.0746       |
+---------------+-------------------------------+----------+----------+--------------+
| c6            | 80.98                         | 13       | 2        | 1.0746       |

推荐阅读