FOR XML EXPLICIT and optional attributes

If attributes of an element are optional can XML Explicit be used to
only return such attrubutes if their value is not null. eg. to return
something like:
<Person Name = "Paul" BicycleBrand = "Raliegh">
<Person Name = "John" CarBrand = "Ford">
Rather than
<Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
<Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
?It is the default behaviour of FOR XML EXPLICIT to map null value to absent
attributes.
Best regards
Michael
<paul.l@.paloma.co.uk> wrote in message
news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
> If attributes of an element are optional can XML Explicit be used to
> only return such attrubutes if their value is not null. eg. to return
> something like:
> <Person Name = "Paul" BicycleBrand = "Raliegh">
> <Person Name = "John" CarBrand = "Ford">
> Rather than
> <Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
> <Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
> ?
>|||Thanks
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
to
return|||Thanks. Thought it might be simple.
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
to
return

FOR XML EXPLICIT and optional attributes

If attributes of an element are optional can XML Explicit be used to
only return such attrubutes if their value is not null. eg. to return
something like:
<Person Name = "Paul" BicycleBrand = "Raliegh">
<Person Name = "John" CarBrand = "Ford">
Rather than
<Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
<Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
?
It is the default behaviour of FOR XML EXPLICIT to map null value to absent
attributes.
Best regards
Michael
<paul.l@.paloma.co.uk> wrote in message
news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
> If attributes of an element are optional can XML Explicit be used to
> only return such attrubutes if their value is not null. eg. to return
> something like:
> <Person Name = "Paul" BicycleBrand = "Raliegh">
> <Person Name = "John" CarBrand = "Ford">
> Rather than
> <Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
> <Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
> ?
>
|||Thanks
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent[vbcol=seagreen]
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
to[vbcol=seagreen]
return[vbcol=seagreen]
|||Thanks. Thought it might be simple.
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent[vbcol=seagreen]
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
to[vbcol=seagreen]
return[vbcol=seagreen]

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