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

loading...


0

我是XML操作的新手,请看下面的代码 .

我有一个像下面的代码

注意:我通过添加@ m = '2'使用了与13个UNION相同的代码,
@ m = '3',@ m = '4',@ m = '5',.........,@ m = '13' . 在每个连续的代码块中 .

有没有办法如何使用CROSS APPLY或任何其他简化方法简化此代码?

以下是此表的模式,其中包含db <>小提琴中的实际数据:here

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')

有人可以提供一种方法来支持这些代码吗?提前致谢!

2回答

  • 1

    对不起,但是这个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       |
    

  • 1

    好吧,我的第一次尝试过于复杂......所以我以非常务实的方式提出了第二个答案:

    --take away the numbers you do not need
    WITH Numbers AS (SELECT * FROM(VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
                                        ,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)) A(Nmbr))
    SELECT 
    RECID as Branch,
    Nmbr AS CallingForM,
    XMLRECORD.value('(/row/c2/text())[1]', 'nvarchar(10)') as UpdateDate,
    XMLRECORD.value('(/row/c3[@m=sql:column("Nmbr")]/text())[1]', 'nvarchar(10)') as Currency,
    XMLRECORD.value('(/row/c6[@m=sql:column("Nmbr")]/text())[1]', 'nvarchar(10)') as [MidRevealRate_CCY_Market-CASH],
    XMLRECORD.value('(/row/c8[@m=sql:column("Nmbr")]/text())[1]', 'nvarchar(10)') as [BuyRate_CCY_Market-IFT],
    XMLRECORD.value('(/row/c8[@m=sql:column("Nmbr")][@s="2"]/text())[1]', 'nvarchar(10)') as [BuyRate_CCY_Market-CASH],
    XMLRECORD.value('(/row/c9[@m=sql:column("Nmbr")]/text())[1]', 'nvarchar(10)') as [SellRate_CCY_Market-CASH]
    FROM [FBNK_PT_CURRENCY]
    CROSS JOIN Numbers n
    WHERE RECID in ('VU0010002')
    

    我们可以添加一个计数表(即时创建), CROSS JOIN (为了获得每个记录的多行),并使用 sql:column() 来使用此值代替您的值,而不是仅使用"m"的不同值来合并多个查询 m 的硬编码值 .

    结果

    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | Branch    | CallingForM | UpdateDate | Currency | MidRevealRate_CCY_Market-CASH | BuyRate_CCY_Market-IFT | BuyRate_CCY_Market-CASH | SellRate_CCY_Market-CASH |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 1           | 20181210   | NULL     | 80.98                         | 79.39                  | 79.39                   | 84.37                    |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 2           | 20181210   | EUR      | 128.39                        | 127.17                 | 119.52                  | 132.17                   |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 3           | 20181210   | FJD      | 53.22                         | 51.58                  | 49.99                   | 55.57                    |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 4           | 20181210   | GBP      | 143.07                        | 141.73                 | 134.44                  | 146.31                   |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 5           | 20181210   | HKD      | 14.46                         | 13.79                  | 11.12                   | 15.16                    |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 6           | 20181210   | JPY      | 1.0008                        | 0.9915                 | 0.8796                  | 1.038                    |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 7           | 20181210   | NZD      | 77.26                         | 76.58                  | 74.19                   | 80.89                    |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 8           | 20181210   | PGK      | 34.34                         | 32.25                  | 25.71                   | 35.85                    |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 9           | 20181210   | SBD      | 14.64                         | 13.97                  | 13.97                   | 15.84                    |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 10          | 20181210   | SGD      | 82.41                         | 81.42                  | 64.46                   | 85.2                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 11          | 20181210   | USD      | 112.6                         | 111.43                 | 109.05                  | 117.6                    |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 12          | 20181210   | VUV      | NULL                          | NULL                   | NULL                    | NULL                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 13          | 20181210   | XPF      | 1.0746                        | 1.0646                 | 0.938                   | 1.1119                   |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 14          | 20181210   | NULL     | NULL                          | NULL                   | NULL                    | NULL                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 15          | 20181210   | NULL     | NULL                          | NULL                   | NULL                    | NULL                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 16          | 20181210   | NULL     | NULL                          | NULL                   | NULL                    | NULL                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 17          | 20181210   | NULL     | NULL                          | NULL                   | NULL                    | NULL                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 18          | 20181210   | NULL     | NULL                          | NULL                   | NULL                    | NULL                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 19          | 20181210   | NULL     | NULL                          | NULL                   | NULL                    | NULL                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    | VU0010002 | 20          | 20181210   | NULL     | NULL                          | NULL                   | NULL                    | NULL                     |
    +-----------+-------------+------------+----------+-------------------------------+------------------------+-------------------------+--------------------------+
    
评论

loading...

暂时没有评论!