I am trying to get a hierarchy XML output from SQL server using 'FOR XML PATH' query. It works in SQL Server but in webfocus it returns an error message:


(FOC1400) SQLCODE IS 6819 (HEX: 00001AA3) XOPEN: 42000
: Microsoft SQL Native Client: [42000] The FOR XML clause is not allowed i
: n a CURSOR statement.

The webfocus version is 7.6.x. Does higher webfocus version support this?

This message has been edited. Last edited by: bug,

I could test it for you in 8105 if you provide code that works with generic data in SQL server like Northwind.

You might also get the same results with
ON TABLE PCHOLD FORMAT XML

This message has been edited. Last edited by: BabakNYC,

quote:

Originally posted by BabakNYC:
I could test it for you in 8105 if you provide code that works with generic data in SQL server like Northwind.

You might also get the same results with
ON TABLE PCHOLD FORMAT XML

"ON TABLE PCHOLD FORMAT XML" outputs flat XML. I want a hierarchy XML that can only be done on SQL server.

Can you try any SQL select statement, just add

FOR XML AUTO

at the end of that statement?

Thanks.

I tested in 8105. I don't get an error when I run this SQL:

ENGINE SQLMSS SET DEFAULT_CONNECTION TEST
SQL SQLMSS PREPARE SQLOUT FOR
select orderid, CustomerID from [dbo].[Orders]
FOR XML AUTO
END
TABLE FILE SQLOUT
PRINT *
END

However, the output is a single BLOB column with content I can't decipher. The column TITLE is similar to what I see in SQL Server but printing it returns data I don't recognize. But PRINT of a BLOB column may be the reason why I get these

I ran this code in 8.0.09:

ENGINE SQLMSS SET DEFAULT_CONNECTION SNAKE
SQL SQLMSS PREPARE SQLOUT FOR
SELECT [BI_PROJECT_BY]
      ,[BI_AWARD_BY]
      ,[PROJECT_ID]
      ,[BI_BUDGET_YEAR]
      ,[DESCR]
  FROM [FMSIBIDEV].[dbo].[BI_DM_DETAIL]
  WHERE
  PROJECT_ID IN ('210284','207453','217219') 
  ORDER BY BI_PROJECT_BY, BI_BUDGET_YEAR
FOR XML AUTO
END
-RUN
TABLE FILE SQLOUT
PRINT *
END

The output was:
XML_F52E2B61-18A1-11d1-B105-00805F49916B
2016-01-12-12-35-10t000029s000222bin
2016-01-12-12-35-10t000029s000223bin
2016-01-12-12-35-10t000029s000224bin
2016-01-12-12-35-10t000029s000225bin
2016-01-12-12-35-10t000029s000226bin
2016-01-12-12-35-10t000029s000227bin
2016-01-12-12-35-10t000029s000228bin
2016-01-12-12-35-10t000029s000229bin
2016-01-12-12-35-10t000029s000230bin
2016-01-12-12-35-10t000029s000231bin
2016-01-12-12-35-10t000029s000232bin
2016-01-12-12-35-10t000029s000233bin
2016-01-12-12-35-10t000029s000234bin
2016-01-12-12-35-10t000029s000235bin
2016-01-12-12-35-10t000029s000236bin
2016-01-12-12-35-10t000029s000237bin
2016-01-12-12-35-10t000029s000238bin
2016-01-12-12-35-10t000029s000239bin
2016-01-12-12-35-10t000029s000240bin
2016-01-12-12-35-10t000029s000241bin

I guess that's not possible. Thanks for the test.

quote:

Originally posted by BabakNYC:
I tested in 8105. I don't get an error when I run this SQL:

ENGINE SQLMSS SET DEFAULT_CONNECTION TEST
SQL SQLMSS PREPARE SQLOUT FOR
select orderid, CustomerID from [dbo].[Orders]
FOR XML AUTO
END
TABLE FILE SQLOUT
PRINT *
END

However, the output is a single BLOB column with content I can't decipher. The column TITLE is similar to what I see in SQL Server but printing it returns data I don't recognize. But PRINT of a BLOB column may be the reason why I get these

BabakNYC, Can you use "ON TABLE PCHOLD FORMAT XML" in the last output?