CREATE TABLE "DBA"."Reports_Activity" (
	"FranId" char(4) NOT NULL,
	"RepId" char(10) NOT NULL,
	"Period" char(1) NULL COMPUTE( DBA.f_Get_PeriodCode_by_Dates(StartDate,EndDate) ),
	"StartDate" date NOT NULL,
	"EndDate" date NOT NULL,
	"PrepareDate" timestamp NOT NULL,
	"Version" char(10) NULL,
	"IsEmpty" bit NULL COMPUTE( (if(RepData is null) or(length(RepData) < 18) then 1 else 0 endif) ),
	"RepUID" "uniqueidentifier" NOT NULL DEFAULT newid(*),
	"RepData" "xml" NULL,
	PRIMARY KEY ( "RepUID" )
);

<Report>
<row RepPart="1" PaymentMethod="Cash" sum="2859.0000"/>
<row RepPart="1" PaymentMethod="Checks & Transfers In" sum="8573.7000"/>
<row RepPart="1" PaymentMethod="Credit Cards" sum="7328.7000"/>
<row RepPart="1" PaymentMethod="Loans" sum="0.0000"/>
<row RepPart="4" RowId="3" Gross="18761.4000"/>
<row RepPart="4" RowId="2" Gross="0.0000"/>
<row RepPart="4" RowId="1" Gross="18761.4000"/>
<row RepPart="5"/>
<row RepPart="6" total_revenue="18761.4" total_exp="0" net_exp="18761.4"/>
<row RepPart="7" sum="0"/>
</Report>

CREATE PROCEDURE DBA.F005(in pRepPart numeric( 2 ))
BEGIN
	CASE pRepPart
	WHEN  1  THEN
		Select b.PaymentMethod, isnull(sum(b."sum"), 0 ) as "sum"
			from Reports_Activity a,
			LATERAL(Select PaymentMethod, sum
						FROM OpenXML(a.RepData, '/Report/row')
						with (RepPart integer '@RepPart',
			                  PaymentMethod char( 50 ) '@PaymentMethod',
            			      "sum" money '@sum')
						where RepPart= 1  and "sum" is not null) b
			where a.RepUID in (select RepUID from Requested_Reports)
			group by b.PaymentMethod;


	WHEN  2  THEN
		select b.TransactionType, isnull(sum(b."sum"), 0 ) as "sum"
			from Reports_Activity a,
			LATERAL(Select TransactionType, sum
						FROM OpenXML(a.RepData, '/Report/row')
						with (RepPart integer '@RepPart',
			                  TransactionType char( 50 ) '@TransactionType',
            			      "sum" money '@sum')
						where RepPart= 2  and "sum" is not null) b
			where RepUID in (select RepUID from Requested_Reports)
			group by TransactionType;
// и так далее для всех секций отчета. У каждой секции свой собственный набор полей в резалтсете.

	ELSE
// Клинтская программа запускает хранимку с нулевым кодом секции и получает количество секций в отчете этого типа.
		select  7 ;
	END CASE; 
	
END;