-2

I am trying to convert my DB2 stored procedure to SQL Server and I'm not sure about this xmltable function

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

Above is my sample XML code. Below is my DB2 statement that needs to be converted.

 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)

Can you please share SQL Server equivalent syntax for this? SQL Server version 2014 / 2016 is preferred. Thanks.

nick
  • 323
  • 2
  • 14
  • 2
    Can you add an example of the XML to your question? Without that, you'll just have to look at the SQL Docs and work it out: https://learn.microsoft.com/en-us/sql/t-sql/xml/nodes-method-xml-data-type?view=sql-server-2017 – David Browne - Microsoft Jul 31 '19 at 17:59
  • Thanks @DavidBrowne-Microsoft. I dont have an example of XML. I am just looking to convert this XMLTABLE from DB2 to SQL Server for any example that you know or have.Unfortunately I dont have and dont know where to find one. – nick Jul 31 '19 at 18:17
  • Hello David, I've included sample XML code, please check and let me know if you can suggest something. Thanks. – nick Aug 07 '19 at 14:27

1 Answers1

0

Perhaps something like:

      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)
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67