|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to output table as XML with elements on separate lines and indented?XML, for each column value to be an element, and for each element on a new line and indented. For example: <customer> <CustomerID>ALFKI</CustomerID> <CompanyName>Alfreds Futterkiste</CompanyName> <ContactName>Maria Anders</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Address>Obere Str. 57</Address> <City>Berlin</City> <PostalCode>12209</PostalCode> <Country>Germany</Country> <Phone>030-0074321</Phone> <Fax>030-0076545</Fax> </customer> Using SELECT ...... FOR XML AUTO, ELEMENTS gets close but there are no new lines or indenting. Is there any way to do this within SQL? If not, how can I do it? Hello Laurence,
> I would like to run a SQL command that outputs all the data in a table No and why would want to? Okay, I know, I know, because you want pretty printed > to XML, for each column value to be an element, and for each element > on a new line and indented. For example: XML. The point here is that to an XML parser, the formatting doesn't matter, it gets discarded during processing. Since it doesn't matter to the processor, SQL Server doesn't do it. When you see Managment format it, its just a pretty print choice that tool made. I know that's not what you wanted to hear, trust me. Been there, done that and finally just moved on. Thanks! Kent Tegels DevelopMentor http://staff.develop.com/ktegels/ Oh well. The reason I want the XML to be readable is this (and if anyone has
a better idea for this then let me know): I want to version control the static/meta data in various tables in my application's database. I want as simple a method as possible to populate a table from whatever files are used to store the data in Source Safe, and conversely as simple a method as possible for dumping the data from a table into files. Most importantly I also want to be able to use the Diff function in Source Safe and easily see the differences between versions of the data. I looked at 3 ideas - BCPing the data into delimited files (batch files to load and unload the data), scripting the data as a series of INSERT commands, and scripting the data to XML files. Formatted XML would have been the most readable format for the data, and possibly scripts to load and dump the data would be the simplest. So, any ideas about this? Show quote "Kent Tegels" <kteg***@develop.com> wrote in message news:18f2bcb1ad2a8c946964ca238c5@news.microsoft.com... > Hello Laurence, > >> I would like to run a SQL command that outputs all the data in a table >> to XML, for each column value to be an element, and for each element >> on a new line and indented. For example: > > No and why would want to? Okay, I know, I know, because you want pretty > printed XML. > The point here is that to an XML parser, the formatting doesn't matter, it > gets discarded during processing. Since it doesn't matter to the > processor, SQL Server doesn't do it. When you see Managment format it, its > just a pretty print choice that tool made. > > I know that's not what you wanted to hear, trust me. Been there, done that > and finally just moved on. > Thanks! > Kent Tegels > DevelopMentor > http://staff.develop.com/ktegels/ > > It should be relatively straightforward to build a little app that takes the
XML and does the indentation for you. Basically, it can loop through and when it finds a non-root element tag, it indents by one. When it finds a closing tag, it decrements by one. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Laurence Neville" <laurencenevi***@hotmail.com> wrote in message Oh well. The reason I want the XML to be readable is this (and if anyone hasnews:eUe9mKWeHHA.1824@TK2MSFTNGP06.phx.gbl... a better idea for this then let me know): I want to version control the static/meta data in various tables in my application's database. I want as simple a method as possible to populate a table from whatever files are used to store the data in Source Safe, and conversely as simple a method as possible for dumping the data from a table into files. Most importantly I also want to be able to use the Diff function in Source Safe and easily see the differences between versions of the data. I looked at 3 ideas - BCPing the data into delimited files (batch files to load and unload the data), scripting the data as a series of INSERT commands, and scripting the data to XML files. Formatted XML would have been the most readable format for the data, and possibly scripts to load and dump the data would be the simplest. So, any ideas about this? Show quote "Kent Tegels" <kteg***@develop.com> wrote in message news:18f2bcb1ad2a8c946964ca238c5@news.microsoft.com... > Hello Laurence, > >> I would like to run a SQL command that outputs all the data in a table >> to XML, for each column value to be an element, and for each element >> on a new line and indented. For example: > > No and why would want to? Okay, I know, I know, because you want pretty > printed XML. > The point here is that to an XML parser, the formatting doesn't matter, it > gets discarded during processing. Since it doesn't matter to the > processor, SQL Server doesn't do it. When you see Managment format it, its > just a pretty print choice that tool made. > > I know that's not what you wanted to hear, trust me. Been there, done that > and finally just moved on. > Thanks! > Kent Tegels > DevelopMentor > http://staff.develop.com/ktegels/ > > This sounds useful ... an XSLT that does the formatting:
http://www.xml.com/lpt/a/1681 Now I just need to figure out how to apply the XSLT to the XML output of my query, hopefully all within SQL .... Show quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:%23Xx$QTWeHHA.4172@TK2MSFTNGP05.phx.gbl... > It should be relatively straightforward to build a little app that takes > the > XML and does the indentation for you. Basically, it can loop through and > when it finds a non-root element tag, it indents by one. When it finds a > closing tag, it decrements by one. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Laurence Neville" <laurencenevi***@hotmail.com> wrote in message > news:eUe9mKWeHHA.1824@TK2MSFTNGP06.phx.gbl... > Oh well. The reason I want the XML to be readable is this (and if anyone > has > a better idea for this then let me know): > > I want to version control the static/meta data in various tables in my > application's database. I want as simple a method as possible to populate > a > table from whatever files are used to store the data in Source Safe, and > conversely as simple a method as possible for dumping the data from a > table > into files. Most importantly I also want to be able to use the Diff > function > in Source Safe and easily see the differences between versions of the > data. > > I looked at 3 ideas - BCPing the data into delimited files (batch files to > load and unload the data), scripting the data as a series of INSERT > commands, and scripting the data to XML files. Formatted XML would have > been > the most readable format for the data, and possibly scripts to load and > dump > the data would be the simplest. > > So, any ideas about this? > > "Kent Tegels" <kteg***@develop.com> wrote in message > news:18f2bcb1ad2a8c946964ca238c5@news.microsoft.com... >> Hello Laurence, >> >>> I would like to run a SQL command that outputs all the data in a table >>> to XML, for each column value to be an element, and for each element >>> on a new line and indented. For example: >> >> No and why would want to? Okay, I know, I know, because you want pretty >> printed XML. >> The point here is that to an XML parser, the formatting doesn't matter, >> it >> gets discarded during processing. Since it doesn't matter to the >> processor, SQL Server doesn't do it. When you see Managment format it, >> its >> just a pretty print choice that tool made. >> >> I know that's not what you wanted to hear, trust me. Been there, done >> that >> and finally just moved on. >> Thanks! >> Kent Tegels >> DevelopMentor >> http://staff.develop.com/ktegels/ >> >> > >
http://blogs.msdn.com/mrorke/archive/2005/06/28/433471.aspx
Show quote "Laurence Neville" <laurencenevi***@hotmail.com> wrote in message
news:usbIhIheHHA.284@TK2MSFTNGP05.phx.gbl... > This sounds useful ... an XSLT that does the formatting: > > http://www.xml.com/lpt/a/1681 > > Now I just need to figure out how to apply the XSLT to the XML output of > my query, hopefully all within SQL .... > Thanks Mike. Ideally I'd like something that works on both SQL 2000 and
2005, but after researching it looks like the only way to apply an XSLT to XML is through code, e.g. in an ASP page, VB app etc. Show quote "Mike C#" <x**@xyz.com> wrote in message news:%23D1E7eleHHA.4564@TK2MSFTNGP03.phx.gbl... > http://blogs.msdn.com/mrorke/archive/2005/06/28/433471.aspx > > "Laurence Neville" <laurencenevi***@hotmail.com> wrote in message > news:usbIhIheHHA.284@TK2MSFTNGP05.phx.gbl... >> This sounds useful ... an XSLT that does the formatting: >> >> http://www.xml.com/lpt/a/1681 >> >> Now I just need to figure out how to apply the XSLT to the XML output of >> my query, hopefully all within SQL .... >> > > "Laurence Neville" <laurencenevi***@hotmail.com> wrote in message Anything you could cook up in SQL 2000 would not be worth the effort of news:usglD9oeHHA.3960@TK2MSFTNGP02.phx.gbl... > Thanks Mike. Ideally I'd like something that works on both SQL 2000 and > 2005, but after researching it looks like the only way to apply an XSLT to > XML is through code, e.g. in an ASP page, VB app etc. transporting over to SQL 2005, especially since SQL 2005 has all this great new XML and SQLCLR functionality built right in. The path of least resistance for "either/or" would be via a middle tier webservice, web page, or other application. A SQL 2000-only server-side solution would probably involve a lot of jumping through COM hoops and/or customer extended procedures. Mike C# wrote:
Show quote > "Laurence Neville" <laurencenevi***@hotmail.com> wrote in message Why not try HTMLTidy? (yes, it tidies XML too, of course) There is an > news:usglD9oeHHA.3960@TK2MSFTNGP02.phx.gbl... >> Thanks Mike. Ideally I'd like something that works on both SQL 2000 and >> 2005, but after researching it looks like the only way to apply an XSLT to >> XML is through code, e.g. in an ASP page, VB app etc. > > Anything you could cook up in SQL 2000 would not be worth the effort of > transporting over to SQL 2005, especially since SQL 2005 has all this great > new XML and SQLCLR functionality built right in. The path of least > resistance for "either/or" would be via a middle tier webservice, web page, > or other application. A SQL 2000-only server-side solution would probably > involve a lot of jumping through COM hoops and/or customer extended > procedures. > > example on its use within SQL Server here on Simple-Talk which works a treat. Fast too! http://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ I understand what you want but formatting the XML isn't really going to
help. A file diff and an XML diff are two very different things. There are tools that carry out XML diffs, these are based on the XML infoset though, not on what, in XML, could be just cosmetic changes, such as whether attributes have double or single quotes, not on what a file diff tool would show up. Show quote "Laurence Neville" <laurencenevi***@hotmail.com> wrote in message news:eUe9mKWeHHA.1824@TK2MSFTNGP06.phx.gbl... > Oh well. The reason I want the XML to be readable is this (and if anyone > has a better idea for this then let me know): > > I want to version control the static/meta data in various tables in my > application's database. I want as simple a method as possible to populate > a table from whatever files are used to store the data in Source Safe, and > conversely as simple a method as possible for dumping the data from a > table into files. Most importantly I also want to be able to use the Diff > function in Source Safe and easily see the differences between versions of > the data. > > I looked at 3 ideas - BCPing the data into delimited files (batch files to > load and unload the data), scripting the data as a series of INSERT > commands, and scripting the data to XML files. Formatted XML would have > been the most readable format for the data, and possibly scripts to load > and dump the data would be the simplest. > > So, any ideas about this? > > "Kent Tegels" <kteg***@develop.com> wrote in message > news:18f2bcb1ad2a8c946964ca238c5@news.microsoft.com... >> Hello Laurence, >> >>> I would like to run a SQL command that outputs all the data in a table >>> to XML, for each column value to be an element, and for each element >>> on a new line and indented. For example: >> >> No and why would want to? Okay, I know, I know, because you want pretty >> printed XML. >> The point here is that to an XML parser, the formatting doesn't matter, >> it gets discarded during processing. Since it doesn't matter to the >> processor, SQL Server doesn't do it. When you see Managment format it, >> its just a pretty print choice that tool made. >> >> I know that's not what you wanted to hear, trust me. Been there, done >> that and finally just moved on. >> Thanks! >> Kent Tegels >> DevelopMentor >> http://staff.develop.com/ktegels/ >> >> > >  |
|||||||||||||||||||||||
Other interesting topics