时间戳

首页 > 解决方案 > XML 表 SQL 服务器

sql-server - XML 表 SQL 服务器

问题描述

我正在尝试将我的 DB2 存储过程转换为 SQL Server，但我不确定这个 xmltable 函数

      DECLARE @IN_AR_UTL_DATA_XML   XML
      DECLARE @WS_ACCOUNT_NO          DECIMAL(13,0)
      DECLARE @WS_BILL_NO             INT
      SET @IN_AR_UTL_DATA_XML = '<CUST_CHARGES>
          <AC_NO>50001233</AC_NO>
          <INVOICE_NO>63877</INVOICE_NO>
           <INVOICELINES>
             <INVOICELINE>
                 <INVOICE_ENTITY>V52259108</INVOICE_ENTITY>
                 <ENTITY_TYPE>W</ENTITY_TYPE>
                 <REVENUE_CLASS>7811</REVENUE_CLASS>
                 <SEQUENCE_NO>1</SEQUENCE_NO>
                 <IS_FIXED>false</IS_FIXED>
                 <IS_BACKOUT>true</IS_BACKOUT>
                 <DATE_ORIG_INVOICE>20190801</DATE_ORIG_INVOICE>
                 <CODE_INVOICE_ITM_TYPE>B</CODE_INVOICE_ITM_TYPE>
                <INVOICE_ITEM_TIMESTMP>2018-09- 
        18T09:36:33.703214</INVOICE_ITEM_TIMESTMP>
       <TOTAL_AMOUNT>300.44</TOTAL_AMOUNT>
             <CHARGES>
                 <WATER_CHARGES>0</WATER_CHARGES>
                 <SEWER_CHARGES>300.44</SEWER_CHARGES>
                 <WATER_CHARGE>-222.3</WATER_CHARGE>
                 <SEWER_CHARGE>0</SEWER_CHARGE>
                 <WATER_REFUND>222.3</WATER_REFUND>
                 <SEWER_DISCOUNT>300.44</SEWER_DISCOUNT>
                 <CAP_DISCOUNT>0</CAP_DISCOUNT>
                 <SUR_CHARGE>0</SUR_CHARGE>
           </CHARGES>
  </INVOICELINE>
  <INVOICELINE>
         <INVOICEING_ENTITY>V52259109</INVOICEING_ENTITY>
          <ENTITY_TYPE>W</ENTITY_TYPE>
          <REVENUE_CLASS>611</REVENUE_CLASS>
          <SEQUENCE_NO>2</SEQUENCE_NO>
          <IS_FIXED>false</IS_FIXED>
          <IS_BACKOUT>false</IS_BACKOUT>
          <DATE_ORIG_INVOICE>20180918</DATE_ORIG_INVOICE>
          <CODE_INVOICE_ITM_TYPE>C</CODE_INVOICE_ITM_TYPE>
         <INVOICE_ITEM_TIMESTMP>2018-09- 
      18T09:36:34.238839</INVOICE_ITEM_TIMESTMP>
     <TOTAL_AMOUNT>-938.21</TOTAL_AMOUNT>
        <CHARGES>
        <WATER_CHARGES>0</WATER_CHARGES>
        <SEWER_CHARGES>-938.21</SEWER_CHARGES>
        <WATER_CHARGE>694.2</WATER_CHARGE>
        <SEWER_CHARGE>0</SEWER_CHARGE>
        <WATER_REFUND>-694.2</WATER_REFUND>
        <SEWER_DISCOUNT>-938.21</SEWER_DISCOUNT>
        <CAP_DISCOUNT>0</CAP_DISCOUNT>
        <SUR_CHARGE>0</SUR_CHARGE>
     </CHARGES>
  </INVOICELINE>
  </INVOICELINES>
   </CUST_CHARGES>'

以上是我的示例 XML 代码。下面是我需要转换的 DB2 语句。

 SELECT 
         WS_AC_NO             = TABLE1.XmlCol1.value('AC_NO[1]' ,'DECIMAL(13,0)') 
        ,WS_INVOICE_NO        = TABLE1.XmlCol1.value('INVOICE_NO[1]','SMALLINT')
        --,INVOICE_ENTITY       = TABLE1.XmlCol1.value('@INVOICE_ENTITY','VARCHAR(9)')  -- This is not working.
        ,INVOICE_ENTITY       = TABLE2.XmlCol2.value('INVOICELINES[1]/INVOICELINE[1]/INVOICE_ENTITY[1]','VARCHAR(9)')
        ,ENTITY_TYPE          = TABLE1.XmlCol1.value('INVOICELINES[1]/INVOICELINE[1]/ENTITY_TYPE[1]'   ,'CHAR(1)')
        ,REVENUE_CLASS        = TABLE1.XmlCol1.value('INVOICELINES[1]/INVOICELINE[1]/REVENUE_CLASS[1]' ,'SMALLINT')
        ,SEQUENCE_NO          = TABLE1.XmlCol1.value('INVOICELINES[1]/INVOICELINE[1]/SEQUENCE_NO[1]' ,'SMALLINT')    
        ,TOTAL_AMOUNT         = TABLE1.XmlCol1.value('INVOICELINES[1]/INVOICELINE[1]/TOTAL_AMOUNT[1]' ,'DECIMAL(11,2)')
        ,CHARGE               = TABLE1.XmlCol1.value('INVOICELINES[1]/INVOICELINE[1]/XmlCol[1]','DECIMAL(11,2)')  -- I need all sub charge values
        ,'W' AS CHARGE_TYPE    
        ,1 AS ORDER_SEQUENCE_NO 
        ,DATE_ORIG_BILL       = TABLE1.XmlCol1.value('INVOICELINES[1]/INVOICELINE[1]/DATE_ORIG_BILL[1]'    ,'INT')
        ,CODE_BILL_ITM_TYPE   = TABLE1.XmlCol1.value('INVOICELINES[1]/INVOICELINE[1]/CODE_BILL_ITM_TYPE[1]','VARCHAR(1)')
        ,BILL_ITEM_TIMESTMP   = TABLE1.XmlCol1.value('INVOICELINES[1]/INVOICELINE[1]/BILL_ITEM_TIMESTMP[1]','DATETIME2(6)')
    FROM @IN_AR_UTL_DATA_XML.nodes('/CUAT_CHARGES') TABLE1(XmlCol1)
CROSS APPLY @IN_AR_UTL_DATA_XML.nodes('/CUAT_CHARGES/INVOICELINES/INVOICELINE') 
  TABLE2(XmlCol2)

您能否为此分享 SQL Server 等效语法？首选 SQL Server 版本 2014 / 2016。谢谢。

标签： sql-serverxmltable

解决方案

也许是这样的：

      DECLARE @IN_AR_UTL_DATA_XML   XML
      DECLARE @WS_ACCOUNT_NO          DECIMAL(13,0)
      DECLARE @WS_BILL_NO             INT
      SET @IN_AR_UTL_DATA_XML = '<CUST_CHARGES>
          <AC_NO>50001233</AC_NO>
          <INVOICE_NO>63877</INVOICE_NO>
           <INVOICELINES>
             <INVOICELINE>
                 <INVOICE_ENTITY>V52259108</INVOICE_ENTITY>
                 <ENTITY_TYPE>W</ENTITY_TYPE>
                 <REVENUE_CLASS>7811</REVENUE_CLASS>
                 <SEQUENCE_NO>1</SEQUENCE_NO>
                 <IS_FIXED>false</IS_FIXED>
                 <IS_BACKOUT>true</IS_BACKOUT>
                 <DATE_ORIG_INVOICE>20190801</DATE_ORIG_INVOICE>
                 <CODE_INVOICE_ITM_TYPE>B</CODE_INVOICE_ITM_TYPE>
                <INVOICE_ITEM_TIMESTMP>2018-09- 
        18T09:36:33.703214</INVOICE_ITEM_TIMESTMP>
       <TOTAL_AMOUNT>300.44</TOTAL_AMOUNT>
             <CHARGES>
                 <WATER_CHARGES>0</WATER_CHARGES>
                 <SEWER_CHARGES>300.44</SEWER_CHARGES>
                 <WATER_CHARGE>-222.3</WATER_CHARGE>
                 <SEWER_CHARGE>0</SEWER_CHARGE>
                 <WATER_REFUND>222.3</WATER_REFUND>
                 <SEWER_DISCOUNT>300.44</SEWER_DISCOUNT>
                 <CAP_DISCOUNT>0</CAP_DISCOUNT>
                 <SUR_CHARGE>0</SUR_CHARGE>
           </CHARGES>
  </INVOICELINE>
  <INVOICELINE>
         <INVOICEING_ENTITY>V52259109</INVOICEING_ENTITY>
          <ENTITY_TYPE>W</ENTITY_TYPE>
          <REVENUE_CLASS>611</REVENUE_CLASS>
          <SEQUENCE_NO>2</SEQUENCE_NO>
          <IS_FIXED>false</IS_FIXED>
          <IS_BACKOUT>false</IS_BACKOUT>
          <DATE_ORIG_INVOICE>20180918</DATE_ORIG_INVOICE>
          <CODE_INVOICE_ITM_TYPE>C</CODE_INVOICE_ITM_TYPE>
         <INVOICE_ITEM_TIMESTMP>2018-09- 
      18T09:36:34.238839</INVOICE_ITEM_TIMESTMP>
     <TOTAL_AMOUNT>-938.21</TOTAL_AMOUNT>
        <CHARGES>
        <WATER_CHARGES>0</WATER_CHARGES>
        <SEWER_CHARGES>-938.21</SEWER_CHARGES>
        <WATER_CHARGE>694.2</WATER_CHARGE>
        <SEWER_CHARGE>0</SEWER_CHARGE>
        <WATER_REFUND>-694.2</WATER_REFUND>
        <SEWER_DISCOUNT>-938.21</SEWER_DISCOUNT>
        <CAP_DISCOUNT>0</CAP_DISCOUNT>
        <SUR_CHARGE>0</SUR_CHARGE>
     </CHARGES>
  </INVOICELINE>
  </INVOICELINES>
   </CUST_CHARGES>'

   SELECT 
         WS_AC_NO             = r.chargeNode.value('AC_NO[1]' ,'DECIMAL(13,0)') 
        ,WS_INVOICE_NO        = r.chargeNode.value('INVOICE_NO[1]','INT')
        ,INVOICE_ENTITY       = i.lineNode.value('INVOICE_ENTITY[1]','VARCHAR(9)')  
        ,ENTITY_TYPE          = i.lineNode.value('ENTITY_TYPE[1]'   ,'CHAR(1)')
        ,REVENUE_CLASS        = i.lineNode.value('REVENUE_CLASS[1]' ,'INT')
        ,SEQUENCE_NO          = i.lineNode.value('SEQUENCE_NO[1]' ,'INT')  
        ,TOTAL_AMOUNT         = i.lineNode.value('TOTAL_AMOUNT[1]' ,'DECIMAL(11,2)')
        ,WATER_CHARGES        = i.lineNode.value('(CHARGES/WATER_CHARGE)[1]','DECIMAL(11,2)')  -- I need all sub charge values
        ,'W' AS CHARGE_TYPE    
        ,1 AS ORDER_SEQUENCE_NO 
    FROM @IN_AR_UTL_DATA_XML.nodes('/CUST_CHARGES') r(chargeNode)
   CROSS APPLY chargeNode.nodes('INVOICELINES/INVOICELINE') i(lineNode)

推荐阅读