Forum Discussion

johnnys's avatar
johnnys
New Contributor
7 years ago

Extracting XML data

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.

This is my desired output is this.

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

5 Replies

    • johnnys's avatar
      johnnys
      New Contributor
      7 years ago

      @del, thanks for the help. I was able to get the data out. greatly appreciate the assistance.

  • johnnys's avatar
    johnnys
    New Contributor
    7 years ago

    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>
  • del's avatar
    del
    Contributor III
    7 years ago

    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.

  • johnnys's avatar
    johnnys
    New Contributor
    7 years ago

    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