Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
For cast function please read below thread
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
Thanks for the links but they couldn't help me.
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;
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.
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.