SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Learn XML


Learn XML

Author
Message
Eric M Russell
Eric M Russell
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16354 Visits: 10911
peter-757102 (5/3/2012)
rmechaber (5/3/2012)
peter-757102 (5/3/2012)
They are unable to model their data properly and often do not handle escaping well, causing errors in individual elements. But XML escaping is extremely simple and requires only a few find/replace actions you can do in practically every programming environment. It is truly a lack of basic knowledge that is the culprit here. I developed a toolset to work around the encoding issue by dropping those specific objects of interest that have these errors and keep processing the rest of the document.

Indeed, as an XML novice I found escaping an extremely annoying and frustrating task. (This was to produce XML to be read by PHP for displaying on a website.) In addition, I couldn't find an easy way to write out XML from SQL to a file with an included XML header, so I have used a rather clunky work-around.

Would you have any good links/references about how to handle this?

Thanks,
Rich


There are several ways, but so far I only had to generate XML from the application layer and not SQL. Once you know how and when to escape data, the problem is reduced to nothing more then concatenating strings in a nativly efficient manner when using this method. For SQL specifically there are other ways as well, some likely more efficient (and complex), but I got no experience with them.

You could start with SELECT and the FOR XML clause for simple set representations in XML, but the output does not neccecarily match what you need to generate (XML works case sensitive for example).

Documentation for the FOR XML clause: http://msdn.microsoft.com/en-us/library/ms190922(v=sql.100).aspx

As for exporting data to a file, that is something I fould always lacking from T-SQL. Importing is easy and has faily good support, exporting not so...it would be my number one wish for any new release! I hate DTS and any such overly complex replacement and just expect to be able to specify an output file in my SQL clause and be done with it (for the sake of both sanity and efficiency).

I assume you know the 5 characters that you need to replace (this forum did not allow me to post them as it generated an error)?


I've found articles that mention using BCP to export an XML resultset to a file.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336&whichpage=2


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30075 Visits: 9730
I've been using XML in various database-related solutions for several years. Started with SQL 2005, really.

The main problem I've run into with it has been very, very poor documentation. The SQL Server XQuery documentation started out being worse than useless when I first needed to dig into it. Circular definitions, lack of examples, examples that didn't actually work, and a writing style that assumed you already knew everything there was to know about XML and just need the specifics to Microsoft's implementation of XQuery functions.

The W3C documentation on XML largely assumes you already know everything about XML and are looking to compare implementations to standards, not looking to figure out what the heck the standards mean in the first place.

I think a lot of the problem with XML is that its been documented by people who are very good at getting computers to communicate with each other, but who haven't the faintest clue of how to communicate with other people. Hate to say that, but it really feels like that.

Over the years since I first started dealing with it, I've noticed the documentation has improved steadily in quality, and in quantity. It's a lot easier to find what I need these days than when I started with it.

But it still very much depends on already knowing the subject pretty well before you can even search for answers. If you don't know the term "nodes function", you really can't Google/Bing/whatever "how do I separate rows of XML into rows of tabular data" and get anything that will help you out. Try both searches, see what you get.

It's moved from "you have to be an expert to even ask a question" to "you have to know some of the terminology before you can ask a question". That's a huge improvement, but it's still got a ways to go.

Where I'm at? I know XML in SQL Server well enough to solve some interesting problems in efficient ways, but it's not as easy as it should be.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
peter-757102
peter-757102
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1059 Visits: 2559
Eric M Russell (5/3/2012)
I've found articles that mention using BCP to export an XML resultset to a file.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336&whichpage=2


I know of bcp, and it is (convenience wise) for export a far cry from what BULK INSERT is in T-SQL for imports.
sturner
sturner
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 3259
I think XML has its place but it has been over-hyped and inappropriately used in various situations.

The product requirements should clearly indicate that using XML (for whatever purpose) is advantageous or necessary.

The probability of survival is inversely proportional to the angle of arrival.
TravisDBA
TravisDBA
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2386 Visits: 3069
I agree Steve, that if you are currently a DBA, it is only going to benefit you to know XML not just only now, but also definitely down the road as well. I live in Miami/Fort Lauderdale and for the same exact reasons it definitely benefits me now, as well as in the future, to learn Spanish. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search