Build xml file from table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Build xml file from table

Author

Topic

nord
Posting Yak Master

126 Posts

Posted - 2013-10-07 : 15:32:20

Hi,
I have problem with create xml file from the table ,in structure like that:
<?xml version="1.0" encoding="utf-8" ?>
<EmployeeImportT xmlns="http://epicor.com/EmployeeImport.xsd">
<Employee ActionType="A" EmployeeNumber="23" EmployeeType="H">
<FirstName>Tim</FirstName>
<LastName>Johnson</LastName>
<ActiveStatus>Y</ActiveStatus>
<StatusCode>HIRE</StatusCode>
<DateHired>2010-01-23</DateHired>
<UserName>tjohnson</UserName>
Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 16:03:03

If you can post DDL for the table that has input data, that makes it easier for someone to respond. Shown below is an example where I created a table, but it has only couple of columns - you will need to expand the query in a similar manner for other nodes/attributes

CREATE TABLE #tmp(EmployeeNumber INT, FirstName VARCHAR(32), Lastname VARCHAR(32));
INSERT INTO #tmp VALUES (1,'a','b'),(2,'x','y');

;WITH XMLNAMESPACES (DEFAULT 'http://epicor.com/EmployeeImport.xsd')
SELECT
	EmployeeNumber AS [@EmployeeNumber],
	FirstName,
	LastName
FROM
	#tmp 
FOR XML PATH('Employee'), root ('EmployeeImportT')

DROP TABLE #tmp;

nord
Posting Yak Master

126 Posts

Posted - 2013-10-07 : 16:12:46

I got it ,maybe another way but:
SELECT

PERSONPersonID AS PERSONPersonID,
PERSONLastName AS PERSONLastName,
PERSONFirstName AS PERSONFirstName,
LOCATIONDepartmentCode AS LOCATIONDepartmentCode,
LOCATIONDepartmentDesc AS LOCATIONDepartmentDesc,
STATUSStatusCode AS STATUSStatusCode
from [Yellow_Epicor].[dbo].[ListeEmp]
FOR
XML PATH('Liste_employés_30092013'))
go

I have another question,how I can add to xml file header like this:

<?xml version="1.0" encoding="utf-8" ?>
Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-07 : 16:17:34

I don't think there is a natural way to do that - you might cast it to string and add the string you want at the beginning. At least, that is the only way I know.

nord
Posting Yak Master

126 Posts

Posted - 2013-10-07 : 16:22:18

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources