Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

kkkumar82
Valued Contributor III

Executing query from Qlikview

All,

I have a below query which I am executing from SQL server, it is running fine from there but when I try to execute the same from

Qlik it is throwing error

WITH XMLNAMESPACES (

   'http://www.XXname.com/ConcurSaeExtract' AS xx

   );

select

  CAST(EVENT_PAYLOAD.query('data(/xx:SAE-RECORD/xx:BATCH_ID)') as varchar(250)) BATCH_ID,

record_id

,creation_date

,processed_flag

,event_type

from uat_mulesoft_sql.dbo.HOLX_MULE_EVENT_Q

where 1=1

and EVENT_TYPE = 'EXPENSE_GL'

--and CREATION_DATE > GETDATE() - 1;

The main problem is with the first part "With XML Namespace" and the "Cast function"

Any ideas will help me

5 Replies
prma7799
Honored Contributor III

Re: Executing query from Qlikview

kkkumar82
Valued Contributor III

Re: Executing query from Qlikview

Yes, but the column EventPayload contains all these values for eg

<xx:SAE-RECORD xmlns:xx="http://www.hologic.com/ConcurSaeExtract"><xx:CONSTANT>DETAIL</xx:CONSTANT><xx:BATCH_ID>811</xx:BATCH_ID><xx:BATCH_DATE>2017-06-20</xx:BATCH_DATE><xx:SEQUENCE_NUMBER>1</xx:SEQUENCE_NUMBER><xx:EMPLOYEE_ID>001192</xx:EMPLOYEE_ID><xx:EMPLOYEE_LAST_NAME>BRUCE</xx:EMPLOYEE_LAST_NAME><xx:EMPLOYEE_FIRST_NAME>KATHY</xx:EMPLOYEE_FIRST_NAME><xx:MI/><xx:EMPLOYEE_GROUP_ID>Non-VP US</xx:EMPLOYEE_GROUP_ID><xx:EMPLOYEE_ORG_UNIT_1>01</xx:EMPLOYEE_ORG_UNIT_1><xx:EMPLOYEE_ORG_UNIT_2>200</xx:EMPLOYEE_ORG_UNIT_2><xx:EMPLOYEE_ORG_UNIT_3>2600</xx:EMPLOYEE_ORG_UNIT_3><xx:EMPLOYEE_ORG_UNIT_4>0200</xx:EMPLOYEE_ORG_UNIT_4>

So to extract the values from the columns we use that

kkkumar82
Valued Contributor III

Re: Executing query from Qlikview

Thanks for the links but they couldn't help me.

Partner
Partner

Re: Executing query from Qlikview

Try SQL keyword in the start and you will be all good, I had the same issue,

Just adding the SQL keyword helpeme to execute the total block.

SQL WITH XMLNAMESPACES (

   'http://www.XXname.com/ConcurSaeExtract' AS xx

   );

select

  CAST(EVENT_PAYLOAD.query('data(/xx:SAE-RECORD/xx:BATCH_ID)') as varchar(250)) BATCH_ID,

record_id

,creation_date

,processed_flag

,event_type

from uat_mulesoft_sql.dbo.HOLX_MULE_EVENT_Q

where 1=1

and EVENT_TYPE = 'EXPENSE_GL'

--and CREATION_DATE > GETDATE() - 1;

MVP
MVP

Re: Executing query from Qlikview

I realize that this is an old post but I think it might be good for others that see this question to get some guidance on the issue....

The query that will be sent to SQL Server has to be between a SQL and a semicolon. Everything between the SQL and the ; will be sent to SQL Server via the driver (most often ODBC) that the CONNECT statement in the load script use.

  1. So having a semicolon in the middle of the query is not going to work well.
  2. The semicolon in the middle of the query does not seem to be part of a SQL Server T-SQL syntax. It is most certainly something that a query tool would accept as a separator between SQL statements that can be sent to SQL Server. Each SQL specification in a Qlik load script has to contain one and only one SQL statement.
  3. The query being sent should probably look like this:

SQL

  WITH XMLNAMESPACES ( 'http://www.XXname.com/ConcurSaeExtract' AS xx)
  SELECT

    CAST(EVENT_PAYLOAD.query('data(/xx:SAE-RECORD/xx:BATCH_ID)') as varchar(250)) BATCH_ID

    ,record_id

    ,creation_date

    ,processed_flag

    ,event_type

  FROM uat_mulesoft_sql.dbo.HOLX_MULE_EVENT_Q

  WHERE

    1=1 AND EVENT_TYPE = 'EXPENSE_GL'

  --AND CREATION_DATE > GETDATE() - 1

  FOR XML PATH('xx:root')

;

The last line has been added and and it might be necessary with a different specification after FOR XML. A semicolon has been remove between the WITH..... and the SELECT. According to Microsoft documentation it shouldn't be any semicolons between a WITH and the following SELECT.