Case XML Woes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Case XML Woes

Author

Topic

weeble
Starting Member

2 Posts

Posted - 2006-04-03 : 05:13:34

Hi all, I have been experimenting with the new xml features in 2K5 and have come accross a small problem.

I can select details from my table and output as XML, that's cool. What I want to do is check an input parameter to see if the user wants to include linked reference data. I can do this with the Case When statement. If it's true, I include, if not, I get just the ID.

Problem is, when I get the ref data, it doesn't give me the XML tag as expected.

e.g.

SQL is like this...


select Address.UID, Address.Line1, Address.Line2, Address.Line3, Address.Line4, 
		   Address.Line5, Address.Region, Address.RegionCode,
	CASE 
    WHEN (Select @IncludeRef) = 0 THEN 
	(
		select RefData.LongName 'Country'
		from RefData
		where RefData.RDLinkUID = Address.CountryRDLinkUID 
	)
    WHEN (Select @IncludeRef) = 1 THEN CAST(Address.CountryRDLinkUID As nvarchar)
	END,
	LastEditDateTime, LastEditUserUID, EditXML, XMLExtension
	from Address 
	where Address.UID = @AddressUID
	for xml auto, elements, root('Address')

...
<RegionCode>L5 9YJ</RegionCode>England<PropertyCategory>Business</PropertyCategory><LastEditDateTime>2006-03-23T00:00:00</LastEditDateTime><LastEditUserUID>1</LastEditUserUID></Address>
</Address>

Where "England" should be surrounded by <Country>

Any help would be appreciated.

pootle_flump

1064 Posts

Posted - 2006-04-03 : 05:52:12

Hi

No idea re 2005 XML outputs however... does aliasing your CASE column sort it? i.e.:


select Address.UID, Address.Line1, Address.Line2, Address.Line3, Address.Line4, 
		   Address.Line5, Address.Region, Address.RegionCode,
	CASE 
    WHEN (Select @IncludeRef) = 0 THEN 
	(
		select RefData.LongName 'Country'
		from RefData
		where RefData.RDLinkUID = Address.CountryRDLinkUID 
	)
    WHEN (Select @IncludeRef) = 1 THEN CAST(Address.CountryRDLinkUID As nvarchar)
	END AS Country,
	LastEditDateTime, LastEditUserUID, EditXML, XMLExtension
	from Address 
	where Address.UID = @AddressUID
	for xml auto, elements, root('Address')

weeble
Starting Member

2 Posts

Posted - 2006-04-03 : 06:10:50

:)

Well, that definitely worked, thanks :) I had tried putting the alias in different places, but not after the END!

Thanks for your help mate.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources