FOR XML PATH adding some extra data not in the database

Bit of a weird question (possibly)

I have a sql server database that has amongst other things data
locations (lat,longs) stored in degrees and heights stored in metres

The XSD I have stipulates that the format like this (in XML)

<Location>
<Latitude Units="Degrees">37.0</Latitude>
<Longitude Units="Degrees">-80.0</Longitude>
<Height Units = "m">100</Height>
</Location>

the lat/long units can also be "Radians" and the height can be in km
(but that is not too important for my question).

Ignoring the "Units" attribute for now I can use FOR SQL PATH as such

select dl.Latitude as "Latitude", dl.Longitude as "Longitude",
dl.Height as "Height" from Location dl where dl.DeploymentID = 1 for
xml path('Location'),Type

But if I want to add the attributes (given that the units data is not
in my DB) I can use

select 'Degrees' as "Latitude/@Units", dl.Latitude as
"Latitude",'Degrees' as "Longitude/@Units", dl.Longitude as
"Longitude", 'm' as "Height/@Units", dl.Height as "Height" from
Location dl where dl.DeploymentID = 1 order by EntryID for xml
path('Location'),Type

<Location>
<Latitude Units="Degrees">8.520121449583158e-001</Latitude>
<Longitude Units="Degrees">-6.050532917888742e-002</Longitude>
<Height Units="m">124</Height>
</Location>

All looks fine so far.

But in some cases the height data in the database is null (where that
height is not known/measured) and obviously I want to handle this.

The problem is using the above SQL I get the following for NULL
heights

<Location>
<Latitude Units="Degrees">8.520121449583158e-001</Latitude>
<Longitude Units="Degrees">-6.050532917888742e-002</Longitude>
<Height Units="m"/>
</Location>

What I would ideally want is for the Height not to appear at all in
this case. But for the life of me I cant work out how to do it!

Can anyone offer any help or indeed advice on whether outputting the
Height element like this is considered bad xml practice (I am new to
all this!)

Note: the Height element in marked as minOccurs="0" in the XSD

Many, many thanks for your time.

Try this:

SELECT
'Degrees' as "Latitude/@Units",
dl.Latitude as "Latitude",
'Degrees' as "Longitude/@Units",
dl.Longitude as "Longitude",
CASE WHEN dl.Height IS NOT NULL THEN 'm' END AS "Height/@Units",
dl.Height as "Height"
FROM Location dl
WHERE dl.DeploymentID = 1
ORDER BY EntryID
FOR XML PATH ( 'Location' ), TYPE



"ajtaylor@hushmail.com" wrote:

> Bit of a weird question (possibly)
>
> I have a sql server database that has amongst other things data
> locations (lat,longs) stored in degrees and heights stored in metres
>
> The XSD I have stipulates that the format like this (in XML)
>
> <Location>
> <Latitude Units="Degrees">37.0</Latitude>
> <Longitude Units="Degrees">-80.0</Longitude>
> <Height Units = "m">100</Height>
> </Location>
>
> the lat/long units can also be "Radians" and the height can be in km
> (but that is not too important for my question).
>
> Ignoring the "Units" attribute for now I can use FOR SQL PATH as such
>
> select dl.Latitude as "Latitude", dl.Longitude as "Longitude",
> dl.Height as "Height" from Location dl where dl.DeploymentID = 1 for
> xml path('Location'),Type
>
> But if I want to add the attributes (given that the units data is not
> in my DB) I can use
>
> select 'Degrees' as "Latitude/@Units", dl.Latitude as
> "Latitude",'Degrees' as "Longitude/@Units", dl.Longitude as
> "Longitude", 'm' as "Height/@Units", dl.Height as "Height" from
> Location dl where dl.DeploymentID = 1 order by EntryID for xml
> path('Location'),Type
>
> <Location>
> <Latitude Units="Degrees">8.520121449583158e-001</Latitude>
> <Longitude Units="Degrees">-6.050532917888742e-002</Longitude>
> <Height Units="m">124</Height>
> </Location>
>
> All looks fine so far.
>
> But in some cases the height data in the database is null (where that
> height is not known/measured) and obviously I want to handle this.
>
> The problem is using the above SQL I get the following for NULL
> heights
>
> <Location>
> <Latitude Units="Degrees">8.520121449583158e-001</Latitude>
> <Longitude Units="Degrees">-6.050532917888742e-002</Longitude>
> <Height Units="m"/>
> </Location>
>
> What I would ideally want is for the Height not to appear at all in
> this case. But for the life of me I cant work out how to do it!
>
> Can anyone offer any help or indeed advice on whether outputting the
> Height element like this is considered bad xml practice (I am new to
> all this!)
>
> Note: the Height element in marked as minOccurs="0" in the XSD
>
> Many, many thanks for your time.
>
>
>
>
>