How to write sql query for xml

Question

question

Ranju78 asked Jun 11, '18

How to write sql query for xml

I need to write the sql query to get following out in xml

<importAudits> 
<importAuditItem> 
<auditType>Status</auditType> 
<auditDate>2012-09-25 08:09:37</auditDate>
<auditValue>In Process</auditValue> 
</importAuditItem> 
<importAuditItem> 
<auditType>Status</auditType> 
<auditDate>2013-09-12 11:55:10</auditDate> 
<auditValue>Approved</auditValue> 
</importAuditItem> 
<importAuditItem>
<auditType>Status</auditType> 
<auditDate>2013-10-03 14:52:33</auditDate> 
<auditValue>Complete</auditValue>
</importAuditItem>

sql server 2012

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Jun 11, 2018 at 08:56 AM

The answer may depend on your database schema... Can you provide sample data in the format in which it appears?

1 ·

Answer 1 · 2018-06-15T16:31:13Z

sthatavarthi answered Jun 15, '18

Without having any sample data, it is difficult to provide an answer as suggested by Thomas. Assuming you have data as below try the following

DECLARE @T TABLE
(
	AuditType CHAR(6),
	AuditDate DATETIME,
	AuditValue VARCHAR(10)
);
INSERT INTO @T
VALUES
	('Status','20120925 08:09:37','In Process'),
	('Status','20130912 11:55:10','Approved'),
	('Status','20131003 14:52:33','Complete');
SELECT
	auditType,
	auditDate = CONVERT(VARCHAR,AuditDate,120),
	auditValue
FROM @T
FOR XML PATH('importAuditItem'), ROOT ('importAudits');

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question