FOR XML EXLPLICIT with attributes

Hi,
Can anyone tell me why this is not doing what I want it too please?
It's my first attempt at this, and I'm not sure if I'm doing the right
thing at all!
DECLARE @.RefType char(15)
SET @.RefType = 'Titles'
SELECT 1 as Tag, null as Parent, null as [Reference!1!], null as
[Reference!1!RefType],
null as [Option!2!],null as [Option!2!Value], null as [Option!2!Text]
UNION ALL
SELECT 2 as Tag, 1 as Parent, null, RT.ReferenceType, null, RDV.ID,
rtrim(Type)
FROM ReferenceTypes RT, ReferenceDataValues RDV
WHERE RTRIM(UPPER(RT.ReferenceType)) = RTRIM(UPPER(@.RefType))
AND RT.ID = RDV.ReferenceID
FOR XML EXPLICIT
The output I get is:
<Reference><Option Value="5" Text="Mr"/><Option Value="6"
Text="Mrs"/><Option Value="7" Text="Dr"/><Option Value="8"
Text=""/><Option Value="9" Text="Miss"/></Reference>
and the output I want is:
<Reference RefType="Titles"><Option Value="5" Text="Mr"/><Option
Value="6" Text="Mrs"/><Option Value="7" Text="Dr"/><Option Value="8"
Text=""/><Option Value="9" Text="Miss"/></Reference>
can anyone tell me how to get the Reftype attribute to appear on the
top node please?
All help greatly appreciated and TIA
Jane
You don't need a column for the Reference element if you're going to
retrieve an attribute for it. The following should work:
SELECT 1 as Tag, null as Parent,
ReferenceType as [Reference!1!RefType],
null as [Option!2!Value],
null as [Option!2!Text]
FROM ReferenceTypes
UNION ALL
SELECT 2 as Tag, 1 as Parent,
RT.ReferenceType,
RDV.ID,
rtrim(Type)
FROM ReferenceTypes RT, ReferenceDataValues RDV
WHERE RTRIM(UPPER(RT.ReferenceType)) = RTRIM(UPPER(@.RefType))
AND RT.ID = RDV.ReferenceID
ORDER BY [Reference!1!RefType]
FOR XML EXPLICIT
Hope that helps,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Jane" <fitzfreckle@.yahoo.co.uk> wrote in message
news:b709383b.0404220705.f646e0b@.posting.google.co m...
> Hi,
> Can anyone tell me why this is not doing what I want it too please?
> It's my first attempt at this, and I'm not sure if I'm doing the right
> thing at all!
> DECLARE @.RefType char(15)
> SET @.RefType = 'Titles'
> SELECT 1 as Tag, null as Parent, null as [Reference!1!], null as
> [Reference!1!RefType],
> null as [Option!2!],null as [Option!2!Value], null as [Option!2!Text]
> UNION ALL
> SELECT 2 as Tag, 1 as Parent, null, RT.ReferenceType, null, RDV.ID,
> rtrim(Type)
> FROM ReferenceTypes RT, ReferenceDataValues RDV
> WHERE RTRIM(UPPER(RT.ReferenceType)) = RTRIM(UPPER(@.RefType))
> AND RT.ID = RDV.ReferenceID
> FOR XML EXPLICIT
> The output I get is:
> <Reference><Option Value="5" Text="Mr"/><Option Value="6"
> Text="Mrs"/><Option Value="7" Text="Dr"/><Option Value="8"
> Text=""/><Option Value="9" Text="Miss"/></Reference>
> and the output I want is:
> <Reference RefType="Titles"><Option Value="5" Text="Mr"/><Option
> Value="6" Text="Mrs"/><Option Value="7" Text="Dr"/><Option Value="8"
> Text=""/><Option Value="9" Text="Miss"/></Reference>
> can anyone tell me how to get the Reftype attribute to appear on the
> top node please?
> All help greatly appreciated and TIA
> Jane
|||that's excellent, thank you.
"Graeme Malcolm \(Content Master Ltd.\)" <graemem_cm@.hotmail.com> wrote in message news:<upzEsTKKEHA.3628@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> You don't need a column for the Reference element if you're going to
> retrieve an attribute for it. The following should work:
> SELECT 1 as Tag, null as Parent,
> ReferenceType as [Reference!1!RefType],
> null as [Option!2!Value],
> null as [Option!2!Text]
> FROM ReferenceTypes
> UNION ALL
> SELECT 2 as Tag, 1 as Parent,
> RT.ReferenceType,
> RDV.ID,
> rtrim(Type)
> FROM ReferenceTypes RT, ReferenceDataValues RDV
> WHERE RTRIM(UPPER(RT.ReferenceType)) = RTRIM(UPPER(@.RefType))
> AND RT.ID = RDV.ReferenceID
> ORDER BY [Reference!1!RefType]
> FOR XML EXPLICIT
> Hope that helps,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
> www.microsoft.com/mspress/books/6137.asp
> ----
> "Jane" <fitzfreckle@.yahoo.co.uk> wrote in message
> news:b709383b.0404220705.f646e0b@.posting.google.co m...