|
Subject:
|
XML from SQL Server data
|
|
Posted By:
|
spinout
|
Post Date:
|
9/24/2004 2:09:51 AM
|
Hi There
I'm wondering how it would be possible to create an XML schema that formats XML results returned from a query to a SQL Server in a customized format? Eg. The results would be returned like this:
<People>
<Name>Bart Simpson</Name>
<Age>10</Age>
<City>Springfield</City>
</People>
Rather than the way SQL Server generates it's own XML which is like:
<People name="Bart Simpson" Age="10" City="Springfield"></People>
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
9/24/2004 3:34:17 AM
|
Firstly XML Schema do not format XML, XSLT does that.
Secondly you could use an XSLT to change it from an attribute style to an element style.
Thirdly you could also change the SQL query to return the data as you wish, SQL server can do virtually any format you need. In this instance it maybe as simple as changing the end of the query to read FOR XML AUTO, ELEMENTS.
Which would suit you better a transformation of the results or a modification to the underlying query?
--
Joe (Co-author Beginning XML, 3rd edition)
|
|
Reply By:
|
spinout
|
Reply Date:
|
9/24/2004 8:54:49 PM
|
Thanks for that, it worked perfectly!
Although can I be a pain and ask if I am able to rename the tags?
Currently, the tags are named after the SQL Database columns and I want to be able to give them different names. Is this possible?
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
9/25/2004 3:02:11 AM
|
You have two main choices, the easier option is to alter you SQL query, if you want Name to be FullName:
Select Name as FullName, ...
FROM People FOR XML AUTO, ELEMENTS.
If you choose illegal element names, e.g. with spaces, the SQL Server will use a hex encoding for those, it looks very ugly.
The more difficult option is to use FOR XML EXPLICIT
--
Joe (Co-author Beginning XML, 3rd edition)
|
|
Reply By:
|
spinout
|
Reply Date:
|
9/25/2004 4:49:31 AM
|
Thanks for that!
After browsing your link to MSDN plus doing a 'Google' on FOR XML EXPLICIT I think that this will offer the best flexibility for what I am trying to achieve.
I just have to try and get my head around it first, your not wrong when you say "difficult option" :)
|
|
Reply By:
|
joefawcett
|
Reply Date:
|
9/25/2004 5:18:12 AM
|
Good luck. The main thing to remember when writing the query is that the internal code that produces the xml uses a forward only cursor. This means that you must be careful to order yur results in a way that reflects the nesting.
--
Joe (Co-author Beginning XML, 3rd edition)
|
