Executing query from Qlikview

[kkkumar82]

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

[prma7799]

For cast function please read below thread

Replacement of CAST function in QlikView! | Qlik Community

how to use count and cast functions in qlikview

[kkkumar82]

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]

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

[deepakqsv]

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;

[petter]

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.