?
Solved

Assign XML to variable from FOR XML Path

Posted on 2010-01-05
2
Medium Priority
?
1,577 Views
Last Modified: 2012-05-08
I am trying to assign an XML Variable with a SELECT...FOR XML PATH but I keep getting the error message

"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."


DECLARE @Doc XML,
        DepreciationTangibleFixedAssets INT,
        @Inventory INT,
        @CurrentYear INT

SELECT @CurrentYear = 2009, 
       @Inventory = 2000,
       @DepreciationTangibleFixedAssets = 5000

SELECT @Doc = 
(
SELECT 'DepreciationTangibleFixedAssets' AS '@TYPE', Convert(VARCHAR(20), @DepreciationTangibleFixedAssets ) "*" 
UNION
SELECT 'Inventory' AS '@TYPE', Convert(VARCHAR(20), @Inventory ) "*" 
UNION
SELECT 'CurrentYear' AS '@TYPE', Convert(VARCHAR(20), @CurrentYear ) "*" 
FOR XML PATH( 'ARGUMENT' ), ROOT('ARGUMENT-LIST')
)

SELECT @Doc

Open in new window

0
Comment
Question by:Clinton1978
2 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 26179308
try this
DECLARE @Doc XML,
        @DepreciationTangibleFixedAssets INT,
        @Inventory INT,
        @CurrentYear INT

SELECT @CurrentYear = 2009, 
       @Inventory = 2000,
       @DepreciationTangibleFixedAssets = 5000

SELECT @Doc =
(select * 
from
(
SELECT 'DepreciationTangibleFixedAssets' AS '@TYPE', Convert(VARCHAR(20), @DepreciationTangibleFixedAssets ) "*" 
UNION
SELECT 'Inventory' AS '@TYPE', Convert(VARCHAR(20), @Inventory ) "*" 
UNION
SELECT 'CurrentYear' AS '@TYPE', Convert(VARCHAR(20), @CurrentYear ) "*" 
) a
FOR XML PATH( 'ARGUMENT' ), ROOT('ARGUMENT-LIST')
)
SELECT @Doc

Open in new window

0
 

Author Closing Comment

by:Clinton1978
ID: 31672852
THANK YOU !!!. I cannot beleive it was so simple...None the less thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question