include only attributes of a record which are not null

sqlbulkcopy sql-server tsql

Question

Greetings from stackoverflow,

I'm presently developing a system for exporting data from databases. Every record has over 30 properties, and I only want to export the ones that are really worth anything.

Here is an illustration in case the information is not clear enough:

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

An XML file should be created from these outputs.

If feasible, this ought to be possible for every database record. I was seeking for a function that would determine whether or not to add an attribute to the export file based on whether or not it contains a value. Sadly, I was unable to locate anything similar.

Edit: Up till now, all I've done is write a query to retrieve every available attribute:

CREATE PROCEDURE export @id int AS
BEGIN
SELECT Name,Profession,Country,Salary FROM Employee
WHERE ID = @id;
END
GO
1
0
8/24/2018 9:48:14 AM

Accepted Answer

You may just use the table's drop-down menu to choose whatever you want.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

in the future

<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 provide a row asID using it'sXML data.

1
8/24/2018 10:20:29 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow