query – The Data Queen

I like to use my blog as a library for myself, to be able to easily find solutions I have used before. Here is a simple example of querying an XML string using SQL.

In this example I have inserted one XML string into a temporary table, with two items called pages, and two reference of type ‘book’. The resulting select statements will pull any and all pages from the XML string, and any ‘book’ references. There can be multiple types of references with varying fields, which you can query in the same way, simply be altering the text in the .nodes in the FROM statement.

    DECLARE @xml as XML
    DECLARE @tblXML AS TABLE  (Response xml )
INSERT INTO @tblXML
SELECT '<response>
  <pages>
    <page>
      <id>1376745</id>
      <name>First Page</name>
     </page>
    <page>
      <id>1376746</id>
      <name>Second Page</name>
     </page>  </pages>
  <references>
    <reference>
      <type>book</type>
      <id>425285</id>
      <name>How I met your mother</name>
      <activestatus>false</activestatus>
    </reference>
    <reference>
      <type>book</type>
      <id>425286</id>
      <name>Covered in Bees</name>
      <activestatus>true</activestatus>
    </reference>
  </references>
</response>' as Response


SELECT @xml = Response from @tblXML

            SELECT 
                ID        =    t.item.value('(id)[1]','int'),
                BookName    =    t.item.value('(name)[1]', 'nvarchar(50)'),
                IsActive    =    case when t.item.value('(activestatus)[1]', 'nvarchar(10)') = 'false' THEN cast(0 as bit) ELSE cast(1 as bit) END
            FROM @xml.nodes('/response/references/reference[type/text() = "book"]') as t(item)


            SELECT ID        =    t.item.value('(id)[1]','int'),
                PageName    =    t.item.value('(name)[1]', 'nvarchar(50)')
            FROM @xml.nodes('/response/pages/page') as t(item)

You can also query a table directly rather than storing the XML in a variable by using CROSS APPLY, like this.

            SELECT ID        =    t.item.value('(id)[1]','int'),
                PageName    =    t.item.value('(name)[1]', 'nvarchar(50)')
            FROM @tblXML x
            CROSS APPLY
            Response.nodes ('/response/pages/page') as t(item)

I hope you find this useful.

This is really specific, but I had to hunt around to find out how to report on historical receivables in Great Plains. This will get the multicurrency receivables, but you can modify it to get all receivables.

I’ve written a stored procedure which goes after a cascade of other stored procedures created for the Great Plains reporting. I chose to use a stored procedure so I can pull the data as it is provided by the GP stored proc using the variables that that stored proc requires, and then adjust the data to meet the needs of my report.

USE [yourdatabase]
GO

/****** Object:  StoredProcedure [dbo].[x_report_HistoricalReceivables_MultiCurrency]    Script Date: 5/12/2014 1:24:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[x_report_HistoricalReceivables_MultiCurrency]
@EndDate date
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @RC int
DECLARE @I_dAgingDate datetime
DECLARE @I_cStartCustomerNumber char(15)
DECLARE @I_cEndCustomerNumber char(15)
DECLARE @I_cStartCustomerName char(65)
DECLARE @I_cEndCustomerName char(65)
DECLARE @I_cStartClassID char(15)
DECLARE @I_cEndClassID char(15)
DECLARE @I_cStartSalesPersonID char(15)
DECLARE @I_cEndSalesPersonID char(15)
DECLARE @I_cStartSalesTerritory char(15)
DECLARE @I_cEndSalesTerritory char(15)
DECLARE @I_cStartShortName char(15)
DECLARE @I_cEndShortName char(15)
DECLARE @I_cStartState char(5)
DECLARE @I_cEndState char(5)
DECLARE @I_cStartZipCode char(11)
DECLARE @I_cEndZipCode char(11)
DECLARE @I_cStartPhoneNumber char(21)
DECLARE @I_cEndPhoneNumber char(21)
DECLARE @I_cStartUserDefined char(15)
DECLARE @I_cEndUserDefined char(15)
DECLARE @I_tUsingDocumentDate tinyint
DECLARE @I_dStartDate datetime
DECLARE @I_dEndDate datetime
DECLARE @I_sIncludeBalanceTypes smallint
DECLARE @I_tExcludeNoActivity tinyint
DECLARE @I_tExcludeMultiCurrency tinyint
DECLARE @I_tExcludeZeroBalanceCustomer tinyint
DECLARE @I_tExcludeFullyPaidTrxs tinyint
DECLARE @I_tExcludeCreditBalance tinyint
DECLARE @I_tExcludeUnpostedAppldCrDocs tinyint
DECLARE @I_tConsolidateNAActivity tinyint

--TODO: Set parameter values here.
Set @I_dAgingDate=  @EndDate
Set @I_cStartCustomerNumber=''
Set @I_cEndCustomerNumber='ZZZZZZZZ'
Set @I_cStartCustomerName=''
Set @I_cEndCustomerName='ZZZZZZZZ'
Set @I_cStartClassID=''
Set @I_cEndClassID='ZZZZZZZZ'
Set @I_cStartSalesPersonID=''
Set @I_cEndSalesPersonID='ZZZZZZZZ'
Set @I_cStartSalesTerritory=''
Set @I_cEndSalesTerritory='ZZZZZZZZ'
Set @I_cStartShortName=''
Set @I_cEndShortName='ZZZZZZZZ'
Set @I_cStartState=''
Set @I_cEndState='ZZZZZZZZ'
Set @I_cStartZipCode=''
Set @I_cEndZipCode='ZZZZZZZZ'
Set @I_cStartPhoneNumber=''
Set @I_cEndPhoneNumber='ZZZZZZZZ'
Set @I_cStartUserDefined=''
Set @I_cEndUserDefined='ZZZZZZZZ'
Set @I_tUsingDocumentDate=0
Set @I_dStartDate='1/1/1900'
Set @I_dEndDate=  @EndDate
Set @I_sIncludeBalanceTypes=0
Set @I_tExcludeNoActivity=1
Set @I_tExcludeMultiCurrency=0
Set @I_tExcludeZeroBalanceCustomer=1
Set @I_tExcludeFullyPaidTrxs=1
Set @I_tExcludeCreditBalance=0
Set @I_tExcludeUnpostedAppldCrDocs=1
Set @I_tConsolidateNAActivity=0

CREATE TABLE #AgedReceivables
(
APPLY_AMOUNT NUMERIC(19,5),
AGING_AMOUNT NUMERIC(19,5),
CUSTNMBR char(15),
CUSTNAME char(100),
BALNCTYP int,
USERDEF1 char(50),
CNTCPRSN char(50),
PHONE1 char(50),
SLPRSNID char(50),
SALSTERR char(50),
PYMTRMID char(50),
CRLMTAMT NUMERIC(19,5),
CRLMTPER NUMERIC(19,5),
CRLMTPAM NUMERIC(19,5),
CRLMTTYP int,
CUSTCLAS char(50),
SHRTNAME char(50),
ZIP char(50),
STATE char(50),
CUDSCRIPTN char(50),
AGNGDATE datetime,
CHCUMNUM char(50),
DOCNUMBR char(50),
RMDTYPAL char(50),
DSCRIPTN char(50),
DCURNCYID char(50),
ORTRXAMT NUMERIC(19,5),
CURTRXAM NUMERIC(19,5),
AGNGBUKT char(50),
CASHAMNT NUMERIC(19,5),
COMDLRAM char(50),
SLSAMNT NUMERIC(19,5),
COSTAMNT NUMERIC(19,5),
FRTAMNT NUMERIC(19,5),
MISCAMNT NUMERIC(19,5),
TAXAMNT NUMERIC(19,5),
DISAVAMT NUMERIC(19,5),
DDISTKNAM NUMERIC(19,5),
DWROFAMNT NUMERIC(19,5),
TRXDSCRN char(50),
DOCABREV char(50),
CHEKNMBR char(50),
DOCDATE datetime,
DUEDATE datetime,
GLPOSTDT datetime,
DISCDATE datetime,
POSTDATE datetime,
DINVPDOF datetime,
DCURRNIDX char(50),
DXCHGRATE NUMERIC(19,5),
ORCASAMT NUMERIC(19,5),
ORSLSAMT NUMERIC(19,5),
ORCSTAMT NUMERIC(19,5),
ORDAVAMT NUMERIC(19,5),
ORFRTAMT NUMERIC(19,5),
ORMISCAMT NUMERIC(19,5),
ORTAXAMT NUMERIC(19,5),
ORCTRXAM NUMERIC(19,5),
ORORGTRX NUMERIC(19,5),
DORDISTKN NUMERIC(19,5),
DORWROFAM NUMERIC(19,5),
DDENXRATE NUMERIC(19,5),
DMCTRXSTT NUMERIC(19,5),
Aging_Period_Amount NUMERIC(19,5),
APFRDCNM char(50),
APFRDCTY char(50),
FROMCURR char(50),
APTODCNM char(50),
APTODCTY char(50),
APPTOAMT NUMERIC(19,5),
ACURNCYID char(50),
DATE1 datetime,
POSTED  char(50),
ADISTKNAM  NUMERIC(19,5),
AWROFAMNT NUMERIC(19,5),
PPSAMDED NUMERIC(19,5),
GSTDSAMT NUMERIC(19,5),
ACURRNIDX char(50),
AXCHGRATE  NUMERIC(19,5),
RLGANLOS NUMERIC(19,5),
ORAPTOAM NUMERIC(19,5),
AORDISTKN NUMERIC(19,5),
AORWROFAM NUMERIC(19,5),
ADENXRATE NUMERIC(19,5),
AMCTRXSTT NUMERIC(19,5),
)

INSERT INTO #AgedReceivables
EXECUTE @RC = [dbo].[seermHATBSRSWrapper] 
   @I_dAgingDate
  ,@I_cStartCustomerNumber
  ,@I_cEndCustomerNumber
  ,@I_cStartCustomerName
  ,@I_cEndCustomerName
  ,@I_cStartClassID
  ,@I_cEndClassID
  ,@I_cStartSalesPersonID
  ,@I_cEndSalesPersonID
  ,@I_cStartSalesTerritory
  ,@I_cEndSalesTerritory
  ,@I_cStartShortName
  ,@I_cEndShortName
  ,@I_cStartState
  ,@I_cEndState
  ,@I_cStartZipCode
  ,@I_cEndZipCode
  ,@I_cStartPhoneNumber
  ,@I_cEndPhoneNumber
  ,@I_cStartUserDefined
  ,@I_cEndUserDefined
  ,@I_tUsingDocumentDate
  ,@I_dStartDate
  ,@I_dEndDate
  ,@I_sIncludeBalanceTypes
  ,@I_tExcludeNoActivity
  ,@I_tExcludeMultiCurrency
  ,@I_tExcludeZeroBalanceCustomer
  ,@I_tExcludeFullyPaidTrxs
  ,@I_tExcludeCreditBalance
  ,@I_tExcludeUnpostedAppldCrDocs
  ,@I_tConsolidateNAActivity

SELECT CUSTNMBR, DCURNCYID as CURNCYID, SUM(ORCTRXAM) as ACCTBAL, SUM(CURTRXAM) AS CADEQUIV
FROM #AgedReceivables 
WHERE 
CURTRXAM <> 0 AND
CASE WHEN DCURNCYID = '' THEN 'CAD' ELSE DCURNCYID END <> 'CAD' 
GROUP BY CUSTNMBR, DCURNCYID

DROP TABLE  #AgedReceivables
END

GO

Thanks to Mark Polino for pointing the way in his blog post – http://mpolino.com/gp/weekly-dynamic-ar-hatb-via-SQL/

Tag: query

Querying XML in SQL

GP Historical MultiCurrency Receivables Query