Saving the for Xml Auto Results to Variable in SQL

saving the FOR XML AUTO results to variable in SQL

For example

DECLARE @xml_var XML  
SET @xml_var =  
(
  SELECT   *,
    (  
      SELECT *

      FROM Orders

      WHERE Orders.CustomerID=Customers.CustomerID

      FOR XML AUTO, TYPE

    )

  FROM Customers WHERE CustomerID='ALFKI'

  FOR XML AUTO, TYPE

)

refer to :
http://blogs.msdn.com/sqlprogrammability/articles/576095.aspx

Saving Dynamic query result into variable

Try this

DECLARE @MyOutput NVARCHAR(max) = '';
DECLARE @MyQuery NVARCHAR(max) = 'SELECT @MyOutput = (SELECT s.name FROM sys.databases s FOR XML AUTO)'
DECLARE @ParmDefinition NVARCHAR(500);

SET @ParmDefinition = N'@MyOutput nvarchar(max) OUTPUT';

EXECUTE sp_executesql @MyQuery
    ,@ParmDefinition
    ,@MyOutput = @MyOutput OUTPUT;

SELECT @MyOutput

You will need to replace the variables and tables as required. Let us know.

Store result of dynamic SQL generating XML to XML Variable

You need to assign the result your query to the parameter in the dynamic SQL.

set @sql = N'set @XML = (select * from my.table where tableId = 1 FOR XML AUTO)'
execute sp_executesql @sql, N'@XML XML OUTPUT', @XML OUTPUT

XML EXPLICIT Into Variable

Handle the XML on the variable set not on the query itself.

Declare @tvTable Table (
    id int IDENTITY(1,1)
    ,someThing varchar(100)
    ,otherThing varchar(100)
    ,thisThing varchar(100)
);

Insert  @tvTable
Values  ('stuff', 'blah', 'foo')
        ,('thing', 'data', 'bob');

declare @someVar nvarchar(max)

;with cte as(
Select  [Tag]                       = 1
        ,[PARENT]                   = NULL
        ,[things!1!thingId]         = NULL
        ,[thing!2!thingId!element]  = NULL
        ,[thing!2!thingOne!element] = NULL
        ,[thing!2!thingTwo!cdata]   = NULL
        ,[thing!2!thingThree!cdata] = NULL
UNION ALL
Select  2
        ,1
        ,1
        ,thingId    = id
        ,thingOne   = someThing
        ,thingTwo   = otherThing
        ,thingThree = thisThing
From    @tvTable)

select  @someVar = (select * from cte FOR XML EXPLICIT)

select @someVar

Trying to store result in xml format in variable sql server

Change that from

SET @DiffXML=(select * from

to

SELECT @DiffXML=(select * from

and you will get your result printed

dynamic sql and store output in variable

For anyone else trying to follow the question, here are some sample tables to use

create table emp (a varchar(10), b int, id int identity)
insert emp select 'abc', 1
insert emp select 'def', 2
create table fieldsinfo (tablename sysname, description sysname, fieldname sysname)
insert fieldsinfo select 'emp', 'field 1', 'a'
insert fieldsinfo select 'emp', 'field 2', 'b'

This script stores the generated XML into the variable @XML (original!)

declare @SQL nvarchar(max)
set @SQL = ''

select @SQL = @SQL + FieldName + ' as [' + Description + '], '
from FieldsInfo
where TableName = 'Emp'

set @SQL = 'set @XML = (select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers''))'

declare @Xml xml
exec sp_executesql @SQL, N'@XML xml output', @xml output

select 'I have >>> ', @Xml   -- check contents

Format Table Variable Output with FOR XML AUTO

Try this instead - use FOR XML PATH and define your output structure with the column alias(ses) you use:

SELECT TOP 1
    testsId AS '@testsId'
FROM 
    @testsToRun
FOR XML PATH('testsToRun'), ROOT('testMessage') 

Gives me:

<testMessage>
  <testsToRun testsId="10" />
</testMessage>

How to save SQL query result to XML file on disk

You can also your SQL Server's extended stored procedures to export it to an xml file.

But you would need to configure the sql server before you can use it.

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

Once xp_cmdshel is enabled in the SQL Server. You can use the following command to export the data to an xml file.

EXEC xp_cmdshell 'bcp "SELECT [Created], [Text] FROM [db304].[dbo].[SearchHistory] FOR XML PATH(''Record''), ROOT(''SearchHistory'')" queryout "C:\bcptest.xml" -T -c -t,'

---

Related Topics