This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Accept
Reject
Preferences
FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Extracting XML data

johnnys
New Contributor

‎03-14-2019 01:15 PM

Hi,

I have created a pipeline that issues a REST Post and it successfully executes. Unfortunately, I do not know how to format and parse the result. Below, I am trying to move the results into a table.

image

This is my desired output is this.

image

I have tried to insert a XML Formatter and XML Parser, however, in the end the results are unformatted. Is there a way to do this?

Thanks,

Johnny

0 Kudos
Reply
5 REPLIES 5

johnnys
New Contributor

‎03-15-2019 08:33 AM

If it helps, here is the API request and response definitions

**REQUEST**

POST /6478_c29eaffe-ec17-4e73-b8ed-04a04eabb0b2/appfxwebservice.asmx HTTP/1.1
Host: altrurig04bo3.blackbaudhosting.com
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "Blackbaud.AppFx.WebService.API.1/AdHocQueryProcess"

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <AdHocQueryProcessRequest AdHocQueryID="guid" OriginalAdHocQueryID="guid" QueryViewID="guid" MaxRecords="int" DoReturnData="boolean" SuppressDuplicateRows="boolean" BypassRecordCount="boolean" SuppressPrimaryKeyField="boolean" ContextRecordID="string" Start="int" Limit="int" SearchText="string" IncludeSecuritySQL="boolean" ProcessForListBuilder="boolean" xmlns="Blackbaud.AppFx.WebService.API.1">
      <SelectFields>
        <f ObjectName="string" ColumnName="string" ParentPath="string" DisplayPath="string" AliasName="string" AggregateType="None or Count or Sum or Min or Max or Average" IsIDSetField="boolean" DataListFilterOperator="EqualTo or NotEqualTo or GreaterThan or GreaterThanOrEqualTo or LessThan or LessThanOrEqualTo or OneOf or NotOneOf or Between or NotBetween or IsLike or NotLike or BeginsWith or DoesNotBeginWith or Contains or DoesNotContain or SoundsLike or Under or NotUnder" SmartQueryFilterOperator="EqualTo or NotEqualTo or GreaterThan or GreaterThanOrEqualTo or LessThan or LessThanOrEqualTo or OneOf or NotOneOf or Between or NotBetween or IsLike or NotLike or BeginsWith or DoesNotBeginWith or Contains or DoesNotContain or SoundsLike or Under or NotUnder" IsHidden="boolean">
          <IDSetFieldInfo>
            <ID>guid</ID>
          </IDSetFieldInfo>
        </f>
        <f ObjectName="string" ColumnName="string" ParentPath="string" DisplayPath="string" AliasName="string" AggregateType="None or Count or Sum or Min or Max or Average" IsIDSetField="boolean" DataListFilterOperator="EqualTo or NotEqualTo or GreaterThan or GreaterThanOrEqualTo or LessThan or LessThanOrEqualTo or OneOf or NotOneOf or Between or NotBetween or IsLike or NotLike or BeginsWith or DoesNotBeginWith or Contains or DoesNotContain or SoundsLike or Under or NotUnder" SmartQueryFilterOperator="EqualTo or NotEqualTo or GreaterThan or GreaterThanOrEqualTo or LessThan or LessThanOrEqualTo or OneOf or NotOneOf or Between or NotBetween or IsLike or NotLike or BeginsWith or DoesNotBeginWith or Contains or DoesNotContain or SoundsLike or Under or NotUnder" IsHidden="boolean">
          <IDSetFieldInfo>
            <ID>guid</ID>
          </IDSetFieldInfo>
        </f>
      </SelectFields>
      <FilterFields>
        <f ObjectName="string" ColumnName="string" ParentPath="string" DisplayPath="string" IsIDSetField="boolean" FilterOperator="IsEqualTo or IsNotEqualTo or IsOneOf or IsNotOneOf or IsLessThan or IsLessThanOrEqualTo or IsGreaterThan or IsGreaterThanOrEqualTo or IsBetween or IsNotBetween or IsLike or IsNotLike or IsBlank or IsNotBlank or BeginsWith or DoesNotBeginWith or Contains or DoesNotContain or SoundsLike or Under or NotUnder" CompareType="None or And or Or" UseLeftParenthesis="boolean" UseRightParenthesis="boolean" LeftParenthesisCount="int" RightParenthesisCount="int" IncludeBlanks="boolean" ValueIsOutputField="boolean" MeetsAllCriteria="boolean" MeetsNoneOfTheCriteria="boolean" MeetsNoneOfTheCriteriaNotEqualTo="boolean" IncludeCurrentNode="boolean" DataMartLastRefresh="dateTime">
          <IDSetFieldInfo>
            <ID>guid</ID>
          </IDSetFieldInfo>
          <DateFilterTypes>
            <QueryDateFilterType>SpecificDate or Today or Yesterday or Tomorrow or ThisWeek or LastWeek or NextWeek or ThisMonth or LastMonth or NextMonth or MonthToDate or ThisQuarter or LastQuarter or NextQuarter or QuarterToDate or ThisCalendarYear or LastCalendarYear or NextCalendarYear or CalendarYearToDate or RollingDate</QueryDateFilterType>
            <QueryDateFilterType>SpecificDate or Today or Yesterday or Tomorrow or ThisWeek or LastWeek or NextWeek or ThisMonth or LastMonth or NextMonth or MonthToDate or ThisQuarter or LastQuarter or NextQuarter or QuarterToDate or ThisCalendarYear or LastCalendarYear or NextCalendarYear or CalendarYearToDate or RollingDate</QueryDateFilterType>
          </DateFilterTypes>
          <FuzzyDateFilterTypes>
            <QueryFuzzyDateFilterType>FuzzyDate or ThisMonth or LastMonth or NextMonth or ThisCalendarYear or LastCalendarYear or NextCalendarYear or January or February or March or April or May or June or July or August or September or October or November or December or TodayAnyYear or YesterdayAnyYear or TomorrowAnyYear</QueryFuzzyDateFilterType>
            <QueryFuzzyDateFilterType>FuzzyDate or ThisMonth or LastMonth or NextMonth or ThisCalendarYear or LastCalendarYear or NextCalendarYear or January or February or March or April or May or June or July or August or September or October or November or December or TodayAnyYear or YesterdayAnyYear or TomorrowAnyYear</QueryFuzzyDateFilterType>
          </FuzzyDateFilterTypes>
          <MonthDayFilterTypes>
            <QueryMonthDayFilterType>MonthDay or ThisMonth or LastMonth or NextMonth or January or February or March or April or May or June or July or August or September or October or November or December</QueryMonthDayFilterType>
            <QueryMonthDayFilterType>MonthDay or ThisMonth or LastMonth or NextMonth or January or February or March or April or May or June or July or August or September or October or November or December</QueryMonthDayFilterType>
          </MonthDayFilterTypes>
          <Values>
            <v>string</v>
            <v>string</v>
          </Values>
          <TranslatedValues>
            <v>string</v>
            <v>string</v>
          </TranslatedValues>
          <DataType>String or Integer or Long or TinyInt or Date or Money or Decimal or Boolean or Guid or XML or FuzzyDate or MonthDay or EmailAddress or WebAddress or Binary or SmallInt or HourMinute or Year or DateWithTimeOffset or Time</DataType>
        </f>
        <f ObjectName="string" ColumnName="string" ParentPath="string" DisplayPath="string" IsIDSetField="boolean" FilterOperator="IsEqualTo or IsNotEqualTo or IsOneOf or IsNotOneOf or IsLessThan or IsLessThanOrEqualTo or IsGreaterThan or IsGreaterThanOrEqualTo or IsBetween or IsNotBetween or IsLike or IsNotLike or IsBlank or IsNotBlank or BeginsWith or DoesNotBeginWith or Contains or DoesNotContain or SoundsLike or Under or NotUnder" CompareType="None or And or Or" UseLeftParenthesis="boolean" UseRightParenthesis="boolean" LeftParenthesisCount="int" RightParenthesisCount="int" IncludeBlanks="boolean" ValueIsOutputField="boolean" MeetsAllCriteria="boolean" MeetsNoneOfTheCriteria="boolean" MeetsNoneOfTheCriteriaNotEqualTo="boolean" IncludeCurrentNode="boolean" DataMartLastRefresh="dateTime">
          <IDSetFieldInfo>
            <ID>guid</ID>
          </IDSetFieldInfo>
          <DateFilterTypes>
            <QueryDateFilterType>SpecificDate or Today or Yesterday or Tomorrow or ThisWeek or LastWeek or NextWeek or ThisMonth or LastMonth or NextMonth or MonthToDate or ThisQuarter or LastQuarter or NextQuarter or QuarterToDate or ThisCalendarYear or LastCalendarYear or NextCalendarYear or CalendarYearToDate or RollingDate</QueryDateFilterType>
            <QueryDateFilterType>SpecificDate or Today or Yesterday or Tomorrow or ThisWeek or LastWeek or NextWeek or ThisMonth or LastMonth or NextMonth or MonthToDate or ThisQuarter or LastQuarter or NextQuarter or QuarterToDate or ThisCalendarYear or LastCalendarYear or NextCalendarYear or CalendarYearToDate or RollingDate</QueryDateFilterType>
          </DateFilterTypes>
          <FuzzyDateFilterTypes>
            <QueryFuzzyDateFilterType>FuzzyDate or ThisMonth or LastMonth or NextMonth or ThisCalendarYear or LastCalendarYear or NextCalendarYear or January or February or March or April or May or June or July or August or September or October or November or December or TodayAnyYear or YesterdayAnyYear or TomorrowAnyYear</QueryFuzzyDateFilterType>
            <QueryFuzzyDateFilterType>FuzzyDate or ThisMonth or LastMonth or NextMonth or ThisCalendarYear or LastCalendarYear or NextCalendarYear or January or February or March or April or May or June or July or August or September or October or November or December or TodayAnyYear or YesterdayAnyYear or TomorrowAnyYear</QueryFuzzyDateFilterType>
          </FuzzyDateFilterTypes>
          <MonthDayFilterTypes>
            <QueryMonthDayFilterType>MonthDay or ThisMonth or LastMonth or NextMonth or January or February or March or April or May or June or July or August or September or October or November or December</QueryMonthDayFilterType>
            <QueryMonthDayFilterType>MonthDay or ThisMonth or LastMonth or NextMonth or January or February or March or April or May or June or July or August or September or October or November or December</QueryMonthDayFilterType>
          </MonthDayFilterTypes>
          <Values>
            <v>string</v>
            <v>string</v>
          </Values>
          <TranslatedValues>
            <v>string</v>
            <v>string</v>
          </TranslatedValues>
          <DataType>String or Integer or Long or TinyInt or Date or Money or Decimal or Boolean or Guid or XML or FuzzyDate or MonthDay or EmailAddress or WebAddress or Binary or SmallInt or HourMinute or Year or DateWithTimeOffset or Time</DataType>
        </f>
      </FilterFields>
      <SortFields>
        <f AliasName="string" FieldName="string" SortOrder="Ascending or Descending" />
        <f AliasName="string" FieldName="string" SortOrder="Ascending or Descending" />
      </SortFields>
      <GroupFilterFields>
        <f AggregateType="None or Count or Sum or Min or Max or Average" />
        <f AggregateType="None or Count or Sum or Min or Max or Average" />
      </GroupFilterFields>
      <SecurityContext>
        <SecurityFeatureID>guid</SecurityFeatureID>
        <SecurityFeatureType>None or Form or DataList or RecordOperation or BusinessProcess or Dashboard or SearchList or SimpleDataList or Task or SmartQuery or AdHocQueryView or BatchType or AddCodeTableEntry or UpdateCodeTableEntry or DeleteCodeTableEntry or Batch or BatchTemplate or Kpi or MergeTask or SmartField or GlobalChange or ReportParameter or SystemPrivilege or ConfigurationData or BatchTemplateCustomize or BatchProcessor or Page or MapEntity or ExportDefinition</SecurityFeatureType>
        <RecordContext>
          <RecordID>string</RecordID>
          <RecordType>string</RecordType>
        </RecordContext>
        <AttributeContext>
          <AttributeCategoryID>string</AttributeCategoryID>
        </AttributeContext>
      </SecurityContext>
      <DeferredResultKey>guid</DeferredResultKey>
      <Parameters>
        <Values xmlns="bb_appfx_dataforms">
          <fv ID="string">
            <Value />
            <ValueTranslation>string</ValueTranslation>
          </fv>
          <fv ID="string">
            <Value />
            <ValueTranslation>string</ValueTranslation>
          </fv>
        </Values>
      </Parameters>
      <CancelID>string</CancelID>
    </AdHocQueryProcessRequest>
  </soap:Body>
</soap:Envelope>

**RESPONSE**
HTTP/1.1 200 OK
Content-Type: text/xml; charset=utf-8
Content-Length: length

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Header>
    <ResponseErrorHeader xmlns="Blackbaud.AppFx.WebService.API.1">
      <Name>string</Name>
      <ErrorCode>GeneralError or AccessDenied or InvalidAppDatabaseName or InvalidRequest or BadWebConfig or DbTransactionOutOfSync or DbConnectionError or DataFormError or BadMetaData or ServiceExecutionError or BusinessProcessError or InvalidAPIUse or DatabaseRevisionMismatch or InvalidSiteInfoID or InvalidProductIsHash or ProductListNotLoaded or TimeoutError or ApplicationUserInactive or UnlinkableWSFederationClaim or AppUserAlreadyLinkedToWSFederationClaim or UnlinkableDuplicateEmailAddress</ErrorCode>
      <ErrorText>string</ErrorText>
      <ExceptionDetails>string</ExceptionDetails>
      <RootRequestName>string</RootRequestName>
      <RequestProcessorName>string</RequestProcessorName>
      <DataFormErrorInfo>
        <ErrorCode>GeneralError or InvalidFieldID or InsufficientFields or InvalidFieldValue or ConcurrencyViolation or RecordNotFound or BBPSGatewayProcessingFailure</ErrorCode>
        <ErrorMsg>string</ErrorMsg>
        <InvalidFieldID>string</InvalidFieldID>
        <InvalidFieldReason>RequiredFldMissing or DataTooLong or InvalidDataFormat or ValueGreaterThanMax or ValueLessThanMin or DataNotUnique or InvalidCodeTableEntry or DBForeignKeyFailure or DBConstraintFailure or CustomException</InvalidFieldReason>
        <InvalidFormID>guid</InvalidFormID>
        <InvalidFieldCollectionFieldID>string</InvalidFieldCollectionFieldID>
        <InvalidFieldPath>string</InvalidFieldPath>
        <SQLErrorInfo WasRethrownByStandardHandler="boolean">
          <ErrorMessage>string</ErrorMessage>
          <ErrorNumber>int</ErrorNumber>
          <Severity>int</Severity>
          <State>int</State>
          <Procedure>string</Procedure>
          <LineNumber>int</LineNumber>
          <ReThrowingProcedure>string</ReThrowingProcedure>
          <IsConcurrencyError>boolean</IsConcurrencyError>
          <IsRecordSecurityPermissionError>boolean</IsRecordSecurityPermissionError>
          <Instance>int</Instance>
        </SQLErrorInfo>
      </DataFormErrorInfo>
    </ResponseErrorHeader>
  </soap:Header>
  <soap:Body>
    <AdHocQueryProcessReply RecordTypeID="guid" xmlns="Blackbaud.AppFx.WebService.API.1">
      <Sql>string</Sql>
      <SecuritySql>
        <string>string</string>
        <string>string</string>
      </SecuritySql>
      <Output Name="string" HadMoreResults="boolean" RowCount="int" LastUpdated="dateTime">
        <Fields>
          <f Name="string" DataType="String or Integer or Long or TinyInt or Date or Money or Decimal or Boolean or Guid or XML or FuzzyDate or MonthDay or EmailAddress or WebAddress or Binary or SmallInt or HourMinute or Year or DateWithTimeOffset or Time" IsPrimaryKey="boolean" AllowMonthDayOnFuzzyDate="boolean" IsHidden="boolean" Format="string" FieldID="string" CurrencyFieldID="string" UniqueName="string" IsImageKey="boolean" />
          <f Name="string" DataType="String or Integer or Long or TinyInt or Date or Money or Decimal or Boolean or Guid or XML or FuzzyDate or MonthDay or EmailAddress or WebAddress or Binary or SmallInt or HourMinute or Year or DateWithTimeOffset or Time" IsPrimaryKey="boolean" AllowMonthDayOnFuzzyDate="boolean" IsHidden="boolean" Format="string" FieldID="string" CurrencyFieldID="string" UniqueName="string" IsImageKey="boolean" />
        </Fields>
        <Rows>
          <r>
            <Values xsi:nil="true" />
          </r>
          <r>
            <Values xsi:nil="true" />
          </r>
        </Rows>
      </Output>
      <HasEncryptedFields>boolean</HasEncryptedFields>
      <MartKey>string</MartKey>
      <QueryWasCancelled>boolean</QueryWasCancelled>
    </AdHocQueryProcessReply>
  </soap:Body>
</soap:Envelope>
0 Kudos
Reply

del
Contributor III

‎03-20-2019 02:23 PM

Hi @johnnys, I worked with a similar XML schema a few months back and had to get a little creative with the Expression language to make it work. If you can provide a small file of actual XML response data, I might can provide some assistance.

0 Kudos
Reply

johnnys
New Contributor

‎03-21-2019 08:12 AM

Hi Del,

Thanks for the assistance. Below is the sample response.

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <AdHocQueryProcessReply RecordTypeID="1a80c21f-4de0-4916-aab7-6e7c5e455e8f" xmlns="Blackbaud.AppFx.WebService.API.1">
            <Sql>select  distinct 	[V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Program].[NAME] as [Sales Order Item\Sales Order Item Ticket\Program\Name],
	[V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Tickets].[PRICETYPECODEID_TRANSLATION] as [Price Type],
	count([V_QUERY_SALESORDER\Sales Order Item].[QUANTITY]) as [COUNT(Sales Order Item\Quantity)]
from [dbo].[V_QUERY_SALESORDER] as [V_QUERY_SALESORDER]
left outer join [dbo].[V_QUERY_SALESORDERITEM] as [V_QUERY_SALESORDER\Sales Order Item] on [V_QUERY_SALESORDER].[ID] = [V_QUERY_SALESORDER\Sales Order Item].[SALESORDERID]
left outer join [dbo].[V_QUERY_SALESORDERITEMTICKET] as [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket] on [V_QUERY_SALESORDER\Sales Order Item].[ID] = [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket].[ID]
left outer join [dbo].[V_QUERY_PROGRAM] as [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Program] on [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket].[PROGRAMID] = [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Program].[ID]
left outer join [dbo].[V_QUERY_TICKET] as [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Tickets] on [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket].[ID] = [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Tickets].[SALESORDERITEMTICKETID]
where (
/*Today*/
 [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Tickets].[SCANDATEWITHTIMEOFFSET]
 between 
/*Date range begin*/
( todatetimeoffset((dateadd(hour, - datepart(hour, getdate()), dateadd(minute, -datepart(minute, getdate()), dateadd(second, -datepart(second, getdate()), dateadd(millisecond, - datepart(millisecond, getdate()), getdate()))))), datediff(minute, getutcdate(), getdate())) )
 and 
/*Date range end*/
( todatetimeoffset((dateadd(millisecond, -3, dateadd(day, 1, dateadd(hour, - datepart(hour, getdate()), dateadd(minute, -datepart(minute, getdate()), dateadd(second, -datepart(second, getdate()), dateadd(millisecond, - datepart(millisecond, getdate()), getdate()))))))), datediff(minute, getutcdate(), getdate())) )
 and [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Program].[ID] in (N'fb9edae3-0267-49f3-a1a9-427c949993aa', N'de53d8a6-d2a4-470d-8160-0b2a64acefcf', N'90815afd-ac3a-44a2-bb4a-50cef06ec046', N'3fc1ff7c-6016-441f-92d1-12397c21c96a', N'8119edb5-0618-4f28-b183-497b2439206b', N'34c8fa11-de12-4c00-aa48-69c98878b902', N'5ab49dc8-4ae8-448e-91fa-a837c491b702', N'0772bc5e-2eec-4fdb-b594-c9db61fc8171', N'7cffc691-d3ab-4990-985f-5b2cfd0b9799', N'2794b21d-0637-4741-bbdd-6bbfdd24840d', N'88bba0a4-e84b-4d91-91c2-4c951c199f14', N'69f73b16-315d-479f-8796-869443099bc6', N'1eb36f07-8a02-493d-91af-51fb3b60f6fa', N'36116996-7b3c-42d7-a55a-29dbf80d0c1a', N'b0473f24-dc5a-402f-a556-b7506c850dd2', N'e1761b29-d6b0-4cd2-83cc-71a56c660061', N'754e2fac-602c-433f-8dc9-e47ae9b696c9', N'7a4616ee-6a4a-471d-ac26-d8bb9bffe8f2', N'700031a3-ac85-42ee-909b-f24467b99450', N'c7a0044f-14e6-484a-9d1b-13aa96da0f46', N'24f134bb-a778-4008-82ef-6abe9c7f77e1', N'031a02aa-5490-4676-bda9-8caff5a15c6b', N'fe076660-68bf-4d1a-ad9d-1747a01b455a', N'99f73421-89d8-49fa-b7f4-30390559a5d4', N'42a112f9-a599-423a-93b2-f1875d5a3278', N'6aa586d3-aa86-4e74-89bb-31b058b552ca', N'74a9579f-78dc-40aa-b5c1-e6ff12e0fff1', N'1e09829a-9481-4c5d-b56e-248c5d812366', N'c946b7ac-2762-46b1-b643-b815f2200c24', N'16d63a75-606a-41f0-b2ad-a888ca3307da', N'c839169f-9d46-4b04-92db-c17c4f9fae63', N'2dc22ad4-1ca4-4fb7-8eab-46fe531310d3', N'f9531a22-2e88-4a09-8e7b-40b8e617987b', N'bff60ca1-525d-4324-b1bb-3b9165d1a6eb', N'5b035a0d-2034-4b5c-8c72-bc77397623d8', N'd5c53581-95d9-49a7-a753-46c8d562ebed', N'5269f136-7011-4f88-965c-1017c75cbd68', N'5c49caef-e1da-4006-8675-9f2197e2aaaa', N'cf9974ae-580b-4623-a6f4-604cc605802c', N'e77dd297-4b3e-4f77-8aee-bf50dd6e3454', N'08c6f13e-0773-4f17-b7e7-feeee7f33d0c', N'5fbfddc0-a531-483b-addb-7a50fdefc77b', N'0b315613-2deb-448a-9d93-7b2f9b3fe243', N'8a06920d-890c-4c5f-91d4-8582f213e972', N'e8f81609-5f37-4d14-aa21-a7cc3d5db598', N'5ec77199-d3a3-49ef-b13e-c6e4e29a2cce', N'f3b4bfcb-d054-46e2-92cb-e402e88eab8e', N'06bc0a67-1369-425e-9f6e-17d918f7c1d8', N'9a80c13c-7248-422a-a88b-07e5d65199cd', N'f9531a22-2e88-4a09-8e7b-40b8e617987b', N'8a06920d-890c-4c5f-91d4-8582f213e972', N'08c6f13e-0773-4f17-b7e7-feeee7f33d0c', N'a2366f02-1210-4ace-90a8-5567ed62b96c', N'7132c597-8a90-4507-87ce-200f6df25a2b'))
 or (
/*Today*/
 [V_QUERY_SALESORDER].[TRANSACTIONDATE]
 between 
/*Date range begin*/
dateadd(hour, - datepart(hour, getdate()), dateadd(minute, -datepart(minute, getdate()), dateadd(second, -datepart(second, getdate()), dateadd(millisecond, - datepart(millisecond, getdate()), getdate()))))
 and 
/*Date range end*/
dateadd(millisecond, -3, dateadd(day, 1, dateadd(hour, - datepart(hour, getdate()), dateadd(minute, -datepart(minute, getdate()), dateadd(second, -datepart(second, getdate()), dateadd(millisecond, - datepart(millisecond, getdate()), getdate()))))))
 and [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Program].[ID] in (N'1b87ef14-b89d-4a5c-803f-79124cc2bcd8', N'031a02aa-5490-4676-bda9-8caff5a15c6b'))
 or (
/*Today*/
 [V_QUERY_SALESORDER].[TRANSACTIONDATE]
 between 
/*Date range begin*/
dateadd(hour, - datepart(hour, getdate()), dateadd(minute, -datepart(minute, getdate()), dateadd(second, -datepart(second, getdate()), dateadd(millisecond, - datepart(millisecond, getdate()), getdate()))))
 and 
/*Date range end*/
dateadd(millisecond, -3, dateadd(day, 1, dateadd(hour, - datepart(hour, getdate()), dateadd(minute, -datepart(minute, getdate()), dateadd(second, -datepart(second, getdate()), dateadd(millisecond, - datepart(millisecond, getdate()), getdate()))))))
 and [V_QUERY_SALESORDER].[REFUNDSTATUSTEXT] = N'Not refunded'
 and [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Program].[ID] in (N'3fc1ff7c-6016-441f-92d1-12397c21c96a', N'88bba0a4-e84b-4d91-91c2-4c951c199f14', N'69f73b16-315d-479f-8796-869443099bc6', N'e4908d9d-a305-45a0-8bae-b3e84a5027e1', N'1eb36f07-8a02-493d-91af-51fb3b60f6fa', N'08c6f13e-0773-4f17-b7e7-feeee7f33d0c', N'fe076660-68bf-4d1a-ad9d-1747a01b455a', N'7a4616ee-6a4a-471d-ac26-d8bb9bffe8f2', N'57e93b24-0e1a-47ea-9bb2-4a6e003a0333', N'e7ab5526-30f0-4b47-bbc4-4614ff293951', N'24f134bb-a778-4008-82ef-6abe9c7f77e1', N'5ab49dc8-4ae8-448e-91fa-a837c491b702', N'9a80c13c-7248-422a-a88b-07e5d65199cd', N'5c49caef-e1da-4006-8675-9f2197e2aaaa', N'0458281f-cc4f-42f1-9ae2-1db233d6d86c', N'5ec77199-d3a3-49ef-b13e-c6e4e29a2cce', N'e1761b29-d6b0-4cd2-83cc-71a56c660061', N'2dc22ad4-1ca4-4fb7-8eab-46fe531310d3', N'fe076660-68bf-4d1a-ad9d-1747a01b455a', N'f9531a22-2e88-4a09-8e7b-40b8e617987b', N'bff60ca1-525d-4324-b1bb-3b9165d1a6eb', N'5b035a0d-2034-4b5c-8c72-bc77397623d8', N'd5c53581-95d9-49a7-a753-46c8d562ebed', N'5269f136-7011-4f88-965c-1017c75cbd68', N'cf9974ae-580b-4623-a6f4-604cc605802c', N'e77dd297-4b3e-4f77-8aee-bf50dd6e3454', N'5fbfddc0-a531-483b-addb-7a50fdefc77b', N'0b315613-2deb-448a-9d93-7b2f9b3fe243', N'8a06920d-890c-4c5f-91d4-8582f213e972', N'e8f81609-5f37-4d14-aa21-a7cc3d5db598', N'f3b4bfcb-d054-46e2-92cb-e402e88eab8e', N'06bc0a67-1369-425e-9f6e-17d918f7c1d8', N'fb9edae3-0267-49f3-a1a9-427c949993aa', N'de53d8a6-d2a4-470d-8160-0b2a64acefcf', N'90815afd-ac3a-44a2-bb4a-50cef06ec046', N'8119edb5-0618-4f28-b183-497b2439206b', N'0772bc5e-2eec-4fdb-b594-c9db61fc8171', N'7cffc691-d3ab-4990-985f-5b2cfd0b9799', N'cb5c67ee-2254-43e8-8f42-2bf1717c0e17', N'b0473f24-dc5a-402f-a556-b7506c850dd2', N'2f23fadc-8eef-4957-9df6-92430d0b3aae', N'754e2fac-602c-433f-8dc9-e47ae9b696c9', N'700031a3-ac85-42ee-909b-f24467b99450', N'c7a0044f-14e6-484a-9d1b-13aa96da0f46', N'99f73421-89d8-49fa-b7f4-30390559a5d4', N'42a112f9-a599-423a-93b2-f1875d5a3278', N'74a9579f-78dc-40aa-b5c1-e6ff12e0fff1', N'1e09829a-9481-4c5d-b56e-248c5d812366', N'c946b7ac-2762-46b1-b643-b815f2200c24', N'16d63a75-606a-41f0-b2ad-a888ca3307da', N'c839169f-9d46-4b04-92db-c17c4f9fae63', N'2dc22ad4-1ca4-4fb7-8eab-46fe531310d3')
 and ([V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Tickets].[SCANDATEWITHTIMEOFFSET] is null)
 and [V_QUERY_SALESORDER].[SALESMETHODTYPE] = N'Daily Sales')
group by [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Program].[NAME], [V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Tickets].[PRICETYPECODEID_TRANSLATION]
order by [Sales Order Item\Sales Order Item Ticket\Program\Name] asc
</Sql>
            <Output Name="Prophix - Combined End of day attendance report" RowCount="13" LastUpdated="0001-01-01T00:00:00">
                <Fields>
                    <f Name="Sales Order Item\Sales Order Item Ticket\Program\Name" FieldID="NAME" UniqueName="V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Program\NAME" />
                    <f Name="Price Type" FieldID="PRICETYPECODEID_TRANSLATION" UniqueName="V_QUERY_SALESORDER\Sales Order Item\Sales Order Item Ticket\Tickets\PRICETYPECODEID_TRANSLATION" />
                    <f Name="COUNT(Sales Order Item\Quantity)" DataType="Integer" FieldID="QUANTITY" UniqueName="V_QUERY_SALESORDER\Sales Order Item\QUANTITY" />
                </Fields>
                <Rows>
                    <r>
                        <Values>
                            <v>Consignment Ticket</v>
                            <v>Adult</v>
                            <v>1</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Daily Admission</v>
                            <v>Adult</v>
                            <v>89</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Daily Admission</v>
                            <v>Child</v>
                            <v>42</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Daily Admission</v>
                            <v>Child (2 &amp; Under)</v>
                            <v>8</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Fun Day-Online</v>
                            <v>Zoo Fun Day - Adult</v>
                            <v>19</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Fun Day-Online</v>
                            <v>Zoo Fun Day - Child</v>
                            <v>8</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Fun Day-Onsite</v>
                            <v>Zoo Fun Day - Adult</v>
                            <v>31</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Fun Day-Onsite</v>
                            <v>Zoo Fun Day - Child</v>
                            <v>13</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Member Admission</v>
                            <v>Adult</v>
                            <v>34</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Member Admission</v>
                            <v>Child</v>
                            <v>5</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Member Admission</v>
                            <v>Child (2 &amp; Under)</v>
                            <v>12</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Prepaid School Group</v>
                            <v>Chaperone</v>
                            <v>11</v>
                        </Values>
                    </r>
                    <r>
                        <Values>
                            <v>Prepaid School Group</v>
                            <v>Student</v>
                            <v>2</v>
                        </Values>
                    </r>
                </Rows>
            </Output>
            <HasEncryptedFields>false</HasEncryptedFields>
            <MartKey />
            <QueryWasCancelled>false</QueryWasCancelled>
        </AdHocQueryProcessReply>
    </soap:Body>
</soap:Envelope>

Thanks,

Johnny

0 Kudos
Reply

del
Contributor III

‎03-25-2019 11:07 AM

@johnnys, see if the attached pipeline will provide a good start for what you need. Your schema was different than what I worked with in my case, but close enough I didn’t have to start from scratch.

Community.5067_2019_03_25.slp (20.7 KB)

Community.5067_2019_03_25.slp
Reply