How can use "Select " Get the XML result

Question

Post reply

How can use "Select " Get the XML result

Banbo Bird

SSC Enthusiast

Points: 136
More actions
April 23, 2014 at 11:06 pm

#284813

I use：
Select * from Announce for xml path
the result is in a temp xml file:
<row>
<AnnounceID>2</AnnounceID>
<Title>Do you know</Title>
<Content>I know</Content>
<AddUser>Administrator</AddUser>
<AddTime>2014-04-24T12:55:09</AddTime>
<IsSendToParents>0</IsSendToParents>
<AddUserID>-1</AddUserID>
<ToUser>0</ToUser>
</row>
I want to get the result like this:
select XXX from Announce
print XXX
XXX:
<row>
<AnnounceID>2</AnnounceID>
<Title>Do you know</Title>
<Content>I know</Content>
<AddUser>Administrator</AddUser>
<AddTime>2014-04-24T12:55:09</AddTime>
<IsSendToParents>0</IsSendToParents>
<AddUserID>-1</AddUserID>
<ToUser>0</ToUser>
</row>
use a variable not a file to output the result

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

Here is an example using AdventureWorks2012

😎

USE AdventureWorks2012;

GO

DECLARE @SOH_XML XML;

SELECT @SOH_XML = (

SELECT

SalesOrderID

,RevisionNumber

,OrderDate

,DueDate

,ShipDate

,Status

,OnlineOrderFlag

,SalesOrderNumber

,PurchaseOrderNumber

,AccountNumber

,CustomerID

,SalesPersonID

,TerritoryID

,BillToAddressID

,ShipToAddressID

,ShipMethodID

,CreditCardID

,CreditCardApprovalCode

,CurrencyRateID

,SubTotal

,TaxAmt

,Freight

,TotalDue

,Comment

,ModifiedDate

FROM Sales.SalesOrderHeader SOH

WHERE SOH.SalesOrderID = 61508

FOR XML PATH('ROW'), TYPE);

SELECT @SOH_XML;

PRINT CONVERT(NVARCHAR(MAX),@SOH_XML,3);

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

redcn2004 (4/23/2014)
use a variable not a file to output the result

xml type columns are shown in the SSMS because it easy to read the data just by clicking it. when u convert it into nvarchar/varchar datatype it will show flat string. why u need to convert the data in 1st, it should be in the database with actual datatype.

Banbo Bird SSC Enthusiast Points: 136 More actions · Answer 3

I want to Print all the records to a XML Page,So I Need to convert select result to variable

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

redcn2004 (4/24/2014)
I want to Print all the records to a XML Page,So I Need to convert select result to variable

there is a limitation on Print statement that your value should not be greater then 8000 bytes.

so if you have a small record like Eirikur Eiriksson example, then its okay. but when you remove where clause and try to print the it data will be truncated.