asked
Elements and attributes when using for xml explicit
Hi, I'm using FOR XML EXPLICIT to generate xml file from sql 2000 database... I need to generate something like this ` 150.45 ` How can I do this if I have a table Product with column Price?
After a little messing around, I got it: DECLARE @TestTable TABLE (ProductId int, Price numeric(10, 2), Currency char(3)) INSERT INTO @TestTable SELECT 1, 150.45, 'EUR' SELECT 1 'TAG', NULL 'PARENT', Currency AS 'PRICE!1!currency', Price AS 'PRICE!1!' FROM @TestTable FOR XML EXPLICIT Result: 150.45 If you were to write the select giving the `Price` node a name (after the last "!") it would supply you with a slightly different XML result:
In SQL Server 2000 I don't think you can do it. For SQL Server 2005, you could use: SELECT [currency], CAST(CAST([price] AS nvarchar(40)) AS XML) FROM [sys].[objects] [Object] FOR XML AUTO Here is an example for objects and types: SELECT RTRIM([type]) AS ObjectType, CAST([name] AS XML) FROM [sys].[objects] [Object] FOR XML AUTO
