January 2, 2020 at 11:50 am
hi,
I would like to convert the result of a SELECT FOR XML query into a text field to be able to manipulate the result in my development language.
if there is only one level for my xml. No problem. I use convert or cast as text and it's ok.
But if i have more than one level and i need to use an union. i have got an error message :
Msg 1086, Niveau 15
La clause FOR XML n'est pas valide dans les vues, les fonctions inline, les tables dérivées et les sous-requêtes lorsque celles-ci contiennent un opérateur défini. Pour contourner ce problème, encapsulez la clause SELECT contenant un opérateur défini au moyen d'une syntaxe de table dérivée et appliquez FOR XML à celle-ci.
The FOR XML clause is not valid in views, inline functions, derived tables and subqueries when these contain a defined operator. To work around this problem, wrap the SELECT clause containing an operator defined using derived table syntax and apply FOR XML to it.
i have made a short example to have this result :
<STOCK_OUT>
<Header>
<DateStock>02/01/2020</DateStock>
<FormatDate>103</FormatDate>
<HeureStock>12:45</HeureStock>
<FormatHeure>205</FormatHeure>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
</Header>
</STOCK_OUT>
the SQL
select cast((
select distinct
1 as Tag,
0 as Parent,
convert(varchar(10), getdate(), 103) as [Header!1!DateStock!ELEMENT],
'103' as [Header!1!FormatDate!ELEMENT],
left(convert(varchar(10), getdate(), 108), 5) as [Header!1!HeureStock!ELEMENT],
'205' as [Header!1!FormatHeure!ELEMENT],
NULL as [Line!2!Item!ELEMENT],
NULL as [Line!2!QTY!ELEMENT]
UNION ALL
select top 5
2 as Tag,
1 as Parent,
NULL,
NULL,
NULL,
NULL,
ART_CODE as 'Item',
cast(STK_qte as int) as Qty
from STK_DAT
FOR XML EXPLICIT ,ROOT('STOCK_OUT')
) as TEXT)
thanks
January 2, 2020 at 12:31 pm
hi,
I would like to convert the result of a SELECT FOR XML query into a text field to be able to manipulate the result in my development language.
if there is only one level for my xml. No problem. I use convert or cast as text and it's ok.
But if i have more than one level and i need to use an union. i have got an error message :
Msg 1086, Niveau 15
La clause FOR XML n'est pas valide dans les vues, les fonctions inline, les tables dérivées et les sous-requêtes lorsque celles-ci contiennent un opérateur défini. Pour contourner ce problème, encapsulez la clause SELECT contenant un opérateur défini au moyen d'une syntaxe de table dérivée et appliquez FOR XML à celle-ci.
The FOR XML clause is not valid in views, inline functions, derived tables and subqueries when these contain a defined operator. To work around this problem, wrap the SELECT clause containing an operator defined using derived table syntax and apply FOR XML to it.
i have made a short example to have this result :
<STOCK_OUT>
<Header>
<DateStock>02/01/2020</DateStock>
<FormatDate>103</FormatDate>
<HeureStock>12:45</HeureStock>
<FormatHeure>205</FormatHeure>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
<Line>
<Item>SP-10268363</Item>
<QTY>1</QTY>
</Line>
</Header>
</STOCK_OUT>the SQL
select cast((
select distinct
1 as Tag,
0 as Parent,
convert(varchar(10), getdate(), 103) as [Header!1!DateStock!ELEMENT],
'103' as [Header!1!FormatDate!ELEMENT],
left(convert(varchar(10), getdate(), 108), 5) as [Header!1!HeureStock!ELEMENT],
'205' as [Header!1!FormatHeure!ELEMENT],
NULL as [Line!2!Item!ELEMENT],
NULL as [Line!2!QTY!ELEMENT]
UNION ALL
select top 5
2 as Tag,
1 as Parent,
NULL,
NULL,
NULL,
NULL,
ART_CODE as 'Item',
cast(STK_qte as int) as Qty
from STK_DAT
FOR XML EXPLICIT ,ROOT('STOCK_OUT')
) as TEXT)thanks
Can you please post the DDL (create table) script and sample data as insert statement?
😎
This looks like an elementary problem to me.
January 2, 2020 at 1:34 pm
Same thinks without a table
select cast((
select distinct
1 as Tag,
0 as Parent,
convert(varchar(10), getdate(), 103) as [Header!1!DateStock!ELEMENT],
'103' as [Header!1!FormatDate!ELEMENT],
left(convert(varchar(10), getdate(), 108), 5) as [Header!1!HeureStock!ELEMENT],
'205' as [Header!1!FormatHeure!ELEMENT],
NULL as [Line!2!Item!ELEMENT],
NULL as [Line!2!QTY!ELEMENT]
UNION ALL
select Tag,Parent, DateStock,FormatDate,HeureStock,FormatHeure,Item,QTY
from (values(2 , 1 , NULL,NULL, NULL,NULL,'ITEM1',10),(2, 1, NULL,NULL, NULL,NULL,'ITEM2',20)) a ( Tag,Parent, DateStock,FormatDate,HeureStock,FormatHeure,Item,QTY )
FOR XML EXPLICIT ,ROOT('STOCK_OUT')
) as TEXT)
result :
<STOCK_OUT>
<Header>
<DateStock>02/01/2020</DateStock>
<FormatDate>103</FormatDate>
<HeureStock>14:34</HeureStock>
<FormatHeure>205</FormatHeure>
<Line>
<Item>ITEM1</Item>
<QTY>10</QTY>
</Line>
<Line>
<Item>ITEM2</Item>
<QTY>20</QTY>
</Line>
</Header>
</STOCK_OUT>
January 2, 2020 at 4:12 pm
The problem is the order of precedence of operators. The FOR XML is evaluated before the UNION, but your query requires that it be evaluated after. Use a CTE for the UNION to ensure the proper ordering.
WITH edge_table AS
(
select distinct
1 as Tag,
0 as Parent,
convert(varchar(10), getdate(), 103) as [Header!1!DateStock!ELEMENT],
'103' as [Header!1!FormatDate!ELEMENT],
left(convert(varchar(10), getdate(), 108), 5) as [Header!1!HeureStock!ELEMENT],
'205' as [Header!1!FormatHeure!ELEMENT],
NULL as [Line!2!Item!ELEMENT],
NULL as [Line!2!QTY!ELEMENT]
UNION ALL
select top 5
2 as Tag,
1 as Parent,
NULL,
NULL,
NULL,
NULL,
ART_CODE as 'Item',
cast(STK_qte as int) as Qty
from STK_DAT
)
select cast((
SELECT *
FROM edge_table
FOR XML EXPLICIT ,ROOT('STOCK_OUT')
) as TEXT)
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 3, 2020 at 9:09 am
thanks a lot. It was so simple. You are right : it was a basic problem.
Is there an another way to get the result of a for XML ( without cast ?) ? i use the MSOLEDBSQL driver.
January 4, 2020 at 1:19 pm
Few thoughts:
1. Strongly recommend that you don't use the TEXT data type, better options are either VARCHAR(MAX) or NVARCHAR(MAX)
2. Using FOR XML PATH() is simpler and more legible than FOR XML EXPLICIT
😎
Here is an example using FOR XML PATH
USE TEEST;
GO
SET NOCOUNT ON;
;WITH BASE_DATA AS
(
SELECT
1 as Parent
,CONVERT(VARCHAR(10), getdate(), 103) AS [DateStock]
,'103' as [FormatDate]
,LEFT(CONVERT(VARCHAR(10), getdate(), 108), 5) AS [HeureStock]
,'205' AS [FormatHeure]
)
,ITEM_DATA(Parent, Item,QTY) AS
(
SELECT 1,'ITEM1',10 UNION ALL
SELECT 1,'ITEM2',20
)
SELECT CONVERT(VARCHAR(MAX),(
SELECT
BD.DateStock
,BD.FormatDate
,BD.HeureStock
,BD.FormatHeure
,(
SELECT
IT.Item AS 'Item'
,IT.QTY AS 'QTY'
FROM ITEM_DATA IT
WHERE IT.Parent = BD.Parent
FOR XML PATH('Line'),TYPE
)
FROM BASE_DATA BD
FOR XML PATH('Header'), ROOT('STOCK_OUT')
),0) AS OUT_TEXT;
January 6, 2020 at 9:23 am
Thanks for you contribution. I know path, explicit or Auto. I have just made a simple example in a couple of minutes. In the real life, it's always a little bit more complicate...
My question was not clear enough :
When you use the MSOLEDBSQL driver, Is there a way to retrieve the result of a FOR XML without using a CAST or Convert. What type of field should retrieve the result of a query with FOR XML.
I have tried several case in my language, but none of them work. The only one I found that works is the one with a cast.
When you launch it in MSSMS, you see that the result is not like a standard sql.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply