Wednesday, March 7, 2012

FOR XML Clause TYPE option

Hello,

Could anybody tell me if it's possible to named the result of this query?

SELECT User_Id, Name, Surname, Age, Nationality
FROM [Basic Data] FOR XML PATH, TYPE, elements, root('AllPrimaryData')

The result of this query is an XML Document in a column without a name and I'm trying to name it but I can't.

Could anybody help me with that?

Thanks a lot.

I'm not sure if this will work, but try:

SELECT

User_Id as "@.UserId",

Name as "@.Name",

Surname as "@.SurName",

Age as "@.Age",

Nationality as "@.Nationality"
FROM [Basic Data]

FOR XML PATH, TYPE, elements, root('AllPrimaryData')|||

Try something like this:

select nameOfColumn from (
SELECT User_Id, Name, Surname, Age, Nationality
FROM [Basic Data] FOR XML PATH, TYPE, elements, root('AllPrimaryData')
as myXMLTable(nameOfColumn)

or do it with a CTE:

with myXMLTable(nameOfColumn) as (
SELECT User_Id, Name, Surname, Age, Nationality
FROM [Basic Data] FOR XML PATH, TYPE, elements, root('AllPrimaryData')
)
select nameOfColumn
from myXMLTable

Steve Kass
Drew University
www.stevekass.com

|||

Hi,

Thanks for your response, but it doesn't work.

|||Thanks a lot Steve, your first proposal works fine!

No comments:

Post a Comment