Question
Dear people from stackoverflow
I'm currently working on a mechanism to export data out of a database. Every record has roughly 30 attributes and I only want to export those attributes which do have an actual value.
If the explanation is not precise enough, here's an example:
+----+-----------+------------+---------+--------+
| ID | Name | Profession | Country | Salary |
+----+-----------+------------+---------+--------+
| 1 | John Doe | NULL | USA | 5000 |
+----+-----------+------------+---------+--------+
| 2 | Jane Doe | Painter | NULL | NULL |
+----+-----------+------------+---------+--------+
| 3 | Jonas Doe | Butcher | England | 8000 |
+----+-----------+------------+---------+--------+
Expected outputs:
John Doe: John Doe, USA, 5000
Jane Doe: Jane Doe, Painter
Jonas Doe: Jonas Doe, Butcher, England, 8000
These outputs should be generated in an XML file.
This should be possible with every record in the database if possible. I was looking for a function that would check if an attribute has a value and depending on that, adds it to the export file or not. Sadly I couldn't find anything like that.
Edit: What i did until now is just writing the query to get all the possible attributes:
CREATE PROCEDURE export @id int AS
BEGIN
SELECT Name,Profession,Country,Salary FROM Employee
WHERE ID = @id;
END
GO
Accepted Answer
You can just select everything from the table you want, using FOR XML PATH.
WITH Employee (ID, [Name], Profession, Country, Salary) AS (
SELECT 1, 'John Doe', NULL, 'USA', 5000 UNION ALL
SELECT 2, 'Jane Doe', 'Painter', NULL, NULL UNION ALL
SELECT 3, 'Jonas Doe', 'Butcher', 'England', 8000
)
SELECT *
FROM Employee
FOR XML PATH
would return
<row>
<ID>1</ID>
<Name>John Doe</Name>
<Country>USA</Country>
<Salary>5000</Salary>
</row>
<row>
<ID>2</ID>
<Name>Jane Doe</Name>
<Profession>Painter</Profession>
</row>
<row>
<ID>3</ID>
<Name>Jonas Doe</Name>
<Profession>Butcher</Profession>
<Country>England</Country>
<Salary>8000</Salary>
</row>
EDIT
WITH Employee (ID, [Name], Profession, Country, Salary) AS (
SELECT 1, 'John Doe', NULL, 'USA', 5000 UNION ALL
SELECT 2, 'Jane Doe', 'Painter', NULL, NULL UNION ALL
SELECT 3, 'Jonas Doe', 'Butcher', 'England', 8000
)
SELECT ID, (SELECT * FROM Employee S WHERE S.ID = M.ID FOR XML PATH) [XML]
FROM Employee M
Would return a row per ID with it's XML data.