FOR XML_EXPLICIT ERROR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

FOR XML_EXPLICIT ERROR

Author

Topic

maverickml
Starting Member

1 Post

Posted - 2013-06-03 : 15:18:41

I want to assign the result of a SELECT FOR XML EXPLICIT statement to a XML Variable such as

CREATE PROCEDURE BILLING_RESPONSE
AS
DECLARE @Data AS XML
SET @Data = (SELECT
1 AS Tag,
NULL AS Parent,
NULL AS 'CallTransactions!1!',
NULL AS 'TCALTRS!2!TRS_DAT_TE!cdata',
NULL AS 'TCALTRS!2!TRS_CRT_DT!Element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
NULL,
TRS_DAT_TE,
TRS_CRT_DT
FROM TCALTRS
WHERE TRS_CRT_DT between CONVERT(date,GETDATE()-1) and CONVERT(date,getdate()) and
TRS_DAT_TE like '%(Submit Response)%'
FOR XML EXPLICIT
)

SELECT @DATA
GO

When i execute this query am getting the following error

Msg 1086, Level 15, State 1, Procedure BILLING_RESPONSE, Line 22

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

Any help will be highly apreciated

Thanks,

Logan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-03 : 16:38:24

You have to do what the error message is suggesting - something like shown below - it parses, but not sure if it will give you the right result because I don't have the data to test

CREATE PROCEDURE BILLING_RESPONSE
AS 
 DECLARE @Data AS XML 
;WITH cte AS 
(
SELECT  1 AS Tag ,
        NULL AS Parent ,
        NULL AS colA ,
        NULL AS colB ,
        NULL AS colC
UNION ALL
SELECT  2 AS Tag ,
        1 AS Parent ,
        NULL ,
        TRS_DAT_TE ,
        TRS_CRT_DT
FROM    TCALTRS
WHERE   TRS_CRT_DT BETWEEN CONVERT(DATE, GETDATE() - 1)
                   AND     CONVERT(DATE, GETDATE())
        AND TRS_DAT_TE LIKE '%(Submit Response)%' 
) 
SELECT
@Data = 
	 (
       SELECT   Tag ,
                Parent ,
                colA AS 'CallTransactions!1!' ,
                colB 'TCALTRS!2!TRS_DAT_TE!cdata' ,
                colC 'TCALTRS!2!TRS_CRT_DT!Element'
       FROM     cte
       FOR      XML EXPLICIT
	  ) ;

Might be easier to use FOR XML PATH. Quoting from MSDN:

The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. Depending on the XML you request, writing EXPLICIT mode queries can be cumbersome. You may find that Using PATH Mode with nesting is a simpler alternative to writing EXPLICIT mode queries. http://msdn.microsoft.com/en-us/library/ms189068.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources