question

Ranju78 avatar image
Ranju78 asked

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 comment
10 |1200

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

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

1 Like 1 ·

1 Answer

·
sthatavarthi avatar image
sthatavarthi answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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