sql-server - XML 到 SQL 表查询

Question

这是我存储在一行中的 XML。如何将其转换为使用以下表格式的 T-SQL 查询插入表中？

<ENVELOPE>

    <DSPVCHDATE>16-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>PRASHANT MEHTA 359244</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Sale</DSPVCHTYPE>
    <DSPINBLOCK>
        <DSPVCHINQTY></DSPVCHINQTY>
        <DSPVCHINAMT></DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
        <DSPVCHOUTQTY>1 Pcs</DSPVCHOUTQTY>
        <DSPVCHNETTOUTAMT>23046.88</DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
        <DSPVCHCLQTY></DSPVCHCLQTY>
        <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2612)</DSPEXPLVCHNUMBER>


    <DSPVCHDATE>19-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>XYZ Company</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Purchase</DSPVCHTYPE>
    <DSPINBLOCK>
        <DSPVCHINQTY>1 Pcs</DSPVCHINQTY>
        <DSPVCHINAMT>23437.50</DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
        <DSPVCHOUTQTY></DSPVCHOUTQTY>
        <DSPVCHNETTOUTAMT></DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
        <DSPVCHCLQTY>0 Pcs</DSPVCHCLQTY>
        <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2613)</DSPEXPLVCHNUMBER>
</ENVELOPE>

这是所需的输出格式。

问题是我在原始 xml 中没有记录分隔符。每条新记录均以<DSPVCHDATE>开头。

Answer 1

这是使用纯 XQuery 的另一种方法。无需进行任何字符串操作、CASTing 等。

根元素内的所有元素 <ENVELOPE>构成算术级数。按位置分组的元素:1 - 7、8 - 14 等应放置在包围的 <row> 内。元素。

它动态创建以下 XML:

<ENVELOPE>
  <row>
    <DSPVCHDATE>16-4-2021</DSPVCHDATE>
    ...
    <DSPEXPLVCHNUMBER>(No. :IV2612)</DSPEXPLVCHNUMBER>
  </row>
  <row>
    <DSPVCHDATE>19-4-2021</DSPVCHDATE>
    ...
    <DSPEXPLVCHNUMBER>(No. :IV2613)</DSPEXPLVCHNUMBER>
  </row>
</ENVELOPE>

SQL

DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<ENVELOPE>

    <DSPVCHDATE>16-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>PRASHANT MEHTA 359244</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Sale</DSPVCHTYPE>
    <DSPINBLOCK>
      <DSPVCHINQTY></DSPVCHINQTY>
      <DSPVCHINAMT></DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
      <DSPVCHOUTQTY>1 Pcs</DSPVCHOUTQTY>
      <DSPVCHNETTOUTAMT>23046.88</DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
      <DSPVCHCLQTY></DSPVCHCLQTY>
      <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2612)</DSPEXPLVCHNUMBER>

    <DSPVCHDATE>19-4-2021</DSPVCHDATE>
    <DSPVCHITEMACCOUNT>XYZ Company</DSPVCHITEMACCOUNT>
    <DSPVCHTYPE>Purchase</DSPVCHTYPE>
    <DSPINBLOCK>
      <DSPVCHINQTY>1 Pcs</DSPVCHINQTY>
      <DSPVCHINAMT>23437.50</DSPVCHINAMT>
    </DSPINBLOCK>
    <DSPOUTBLOCK>
      <DSPVCHOUTQTY></DSPVCHOUTQTY>
      <DSPVCHNETTOUTAMT></DSPVCHNETTOUTAMT>
    </DSPOUTBLOCK>
    <DSPCLBLOCK>
      <DSPVCHCLQTY>0 Pcs</DSPVCHCLQTY>
      <DSPVCHCLAMT></DSPVCHCLAMT>
    </DSPCLBLOCK>
    <DSPEXPLVCHNUMBER>(No. :IV2613)</DSPEXPLVCHNUMBER>

</ENVELOPE>');

SELECT ID --, x
    , c.value('(DSPVCHDATE/text())[1]','nvarchar(100)') as DSPVCHDATE
    ,c.value('(DSPVCHITEMACCOUNT/text())[1]','nvarchar(100)') as DSPVCHITEMACCOUNT
    ,c.value('(DSPVCHTYPE/text())[1]','nvarchar(100)') as DSPVCHTYPE
    ,c.value('(DSPINBLOCK/DSPVCHINQTY/text())[1]','nvarchar(100)') AS DSPVCHINQTY
    ,c.value('(DSPINBLOCK/DSPVCHINAMT/text())[1]','decimal(12,2)') AS DSPVCHINAMT 
    ,c.value('(DSPOUTBLOCK/DSPVCHOUTQTY/text())[1]','nvarchar(100)') AS DSPVCHOUTQTY 
    ,c.value('(DSPOUTBLOCK/DSPVCHNETTOUTAMT/text())[1]','decimal(12,2)') AS DSPVCHNETTOUTAMT
    ,c.value('(DSPEXPLVCHNUMBER/text())[1]','nvarchar(100)') as DSPEXPLVCHNUMBER
    --,c.value('(DSPCLBLOCK/DSPVCHCLQTY/text())[1]','nvarchar(100)') AS DSPVCHCLQTY 
    --,c.value('(DSPCLBLOCK/DSPVCHCLAMT/text())[1]','int') AS DSPVCHCLAMT 
FROM @tbl
CROSS APPLY (SELECT xmldata.query('<ENVELOPE>
    {
        for $x in /ENVELOPE/DSPVCHDATE
        let $pos := count(ENVELOPE/DSPVCHDATE[. << $x]) + 1
        let $start := 1 + 7 * ($pos -1)
        let $end := 7 * $pos
        return <row>{/ENVELOPE/*[position() ge $start and position() le $end]}</row>
    }
    </ENVELOPE>')) AS t1(x)
CROSS APPLY t1.x.nodes('/ENVELOPE/row') AS t2(c);

输出

+----+------------+-----------------------+------------+-------------+-------------+--------------+------------------+------------------+
| ID | DSPVCHDATE |   DSPVCHITEMACCOUNT   | DSPVCHTYPE | DSPVCHINQTY | DSPVCHINAMT | DSPVCHOUTQTY | DSPVCHNETTOUTAMT | DSPEXPLVCHNUMBER |
+----+------------+-----------------------+------------+-------------+-------------+--------------+------------------+------------------+
|  1 | 16-4-2021  | PRASHANT MEHTA 359244 | Sale       | NULL        | NULL        | 1 Pcs        | 23046.88         | (No. :IV2612)    |
|  1 | 19-4-2021  | XYZ Company           | Purchase   | 1 Pcs       | 23437.50    | NULL         | NULL             | (No. :IV2613)    |
+----+------------+-----------------------+------------+-------------+-------------+--------------+------------------+------------------+

SQL#2

基于@Charlieface 的想法。

WITH rs AS
(
    SELECT ID, xmldata
        , c.value('for $i in . return count(../*[. << $i]) + 1', 'INT') AS pos
    FROM @tbl
        CROSS APPLY xmldata.nodes('/ENVELOPE/DSPVCHDATE') AS t(c)
) 
SELECT ID
    , c.value('(/ENVELOPE/*[sql:column("pos")]/text())[1]','nvarchar(100)') AS DSPVCHDATE
    , c.value('(/ENVELOPE/*[sql:column("pos") + 1]/text())[1]','nvarchar(100)') AS DSPVCHITEMACCOUNT
    , c.value('(/ENVELOPE/*[sql:column("pos") + 2]/text())[1]','nvarchar(100)') AS DSPVCHTYPE
    , c.value('(/ENVELOPE/*[sql:column("pos") + 3]/DSPVCHINQTY/text())[1]','nvarchar(100)') AS DSPVCHINQTY
    , c.value('(/ENVELOPE/*[sql:column("pos") + 3]/DSPVCHINAMT/text())[1]','decimal(12,2)') AS DSPVCHINAMT 
    , c.value('(/ENVELOPE/*[sql:column("pos") + 4]/DSPVCHOUTQTY/text())[1]','nvarchar(100)') AS DSPVCHOUTQTY 
    , c.value('(/ENVELOPE/*[sql:column("pos") + 4]/DSPVCHNETTOUTAMT/text())[1]','nvarchar(100)') AS DSPVCHNETTOUTAMT
    , c.value('(/ENVELOPE/*[sql:column("pos") + 6]/text())[1]','nvarchar(100)') AS DSPEXPLVCHNUMBER
FROM rs
    CROSS APPLY xmldata.nodes('/ENVELOPE') AS t(c);