Today I found some functionality which I thought was amazing and will save me loads of time creating some sample XML files to test my webservice with.
I had some sample data in database which I wanted to convert into an XML file.
My original idea was to use excel to concatenate bits of it but this turned out to be to slow and difficult. I then found out that SQL Server has in built functionality to convert sql statements into XML files.
This article goes through some of the functionality and I would also read this article if you want more detail than my example below
below is my select statement, the important part is at the end FOR XML AUTO,ELEMENTS this converts the sql statement into XML elements SELECT [Code]"Code" ,[Name]"Name" ,[Commission %]"Commission" ,[E-Mail]"Email" ,[Phone No_]"Phone" ,[Job Title]"JobTitle" FROM [database].[dbo].[Salesperson_Purchaser]salesperson FOR XML AUTO,ELEMENTS this is the result, amazing. I can't believe I have never heard of this fantastic feature before <salesperson> <Code>MT</Code> <Name>Metaphorix</Name> <Commission>0.00000000000000000000</Commission> <Email>metaphorix@metaphorix.co.uk</Email> <Phone></Phone> <JobTitle></JobTitle> </salesperson> <salesperson> <Code>TS</Code> <Name>tom scott</Name> <Commission>0.00000000000000000000</Commission> <Email>tom.email.co.uk</Email> <Phone></Phone> <JobTitle></JobTitle> </salesperson> <salesperson> <Code>DR</Code> <Name>Jerry time</Name> <Commission>0.00000000000000000000</Commission> <Email></Email> <Phone></Phone> <JobTitle></JobTitle> </salesperson>
I tried doing this in Teradata SQL Assistant on one of my database tables and I get the following error SELECT Failed. 3706: Syntax error: expected something between the ‘select’ keyword and ‘[‘.
LikeLike
Thanks for the tip. I am borrowing this for a sql query which needs to have it’s information converted to XML to send to another vendor. However, how do you get the FROM clause to work if you’re or calling multiple tables?
LikeLike
You need to join the tables together
http://www.w3schools.com/sql/sql_join.asp
LikeLike
I noticed that you have everything in quotations after each of the pieces of the SELECT statement, but nothing around the salesperson designation trailing the FROM clause. I’m assuming that it will work the same way with a JOIN as it does without it above.
Thanks again,
LikeLike