XML Data

suesheen

Hi All,

I am in need of some help. I've recently started studying "Microsoft SQL Server 2005 - Implementation and Maintenance" (Exam 70-431)
I got to Chapter 8 - "Managing XML Data" and i've hit a brick wall. Has anyone studied this before? I just can't get my head around it - any explanations greatly appreciated

Dr.Silly

what exactly has stumped you.
why are you doing the sql 2005 exam, you should be doing the 2008 one ?
I recently got the 70-432 and 70-451

Jaych1000

XML is a data exchange langauage. Basically, you transfer data using XML. The big deal with it is that you can define your own custom 'tags'. There are no keywords.

So if you wanted to exchange data about a person you could have:

<name></name>
<age></age>
<nameOfBestFriend></nameOfBestFriend>
<whateverTagYouWant></whateverTagYouWant>

It could be filled out like this:

<name>John</name>
<age>121</age>
<nameOfBestFriend>Terry</nameOfBestFriend>
<whateverTagYouWant>Dog</whateverTagYouWant>

That's the basics of it. The tags don't need to be specific words.
Is that what you were wondering about?

suesheen

thanks a million for both of your replies. To answer your questions, the reason i'm studying 2005 is because we have that installed in our company so its the one i'll be working with on a daily basis

@Jaych1000: I understand XML to an extent (i studied VB in college and HTML, so understand the tags etc)
However, where i get confused is when SQL gets involved. The chapter covers "Retrieving XML Data by using SQL Server Server-side TEchnologies", using SQLXML - annotated XSD Schemas & Converting between XML Data and Relational Data & XML Indexes
Its just quite confusing - its like they squashed a whole book into one chapter and don't explain it very well.

Dr.Silly

suesheen wrote: »

thanks a million for both of your replies. To answer your questions, the reason i'm studying 2005 is because we have that installed in our company so its the one i'll be working with on a daily basis

@Jaych1000: I understand XML to an extent (i studied VB in college and HTML, so understand the tags etc)
However, where i get confused is when SQL gets involved. The chapter covers "Retrieving XML Data by using SQL Server Server-side TEchnologies", using SQLXML - annotated XSD Schemas & Converting between XML Data and Relational Data & XML Indexes
Its just quite confusing - its like they squashed a whole book into one chapter and don't explain it very well.

Can I suggest you have a good read of this and follow the examples, and come back with any more questions you may have ?

If the book you are reading is specifically for the exam, then it will be confusing without going through something like this first.

http://www.15seconds.com/issue/050803.htm

suesheen

thanks for that link - i'll have a look and come back to thread if i get stuck

amen

don't store XML data in the database despite what the tech companies tell you.

It seems a great idea until the scheme changes or you need to search on particular values.

far better to store the data in columns and convert to XML on the way out (SP,TSQL) or have a generic class to convert

suesheen

does this make any sense?

If you want to show SQL Database's on WebPage, it needs to be in XML format. To Query/Process XML you can use: XQuery (Xpath) or SQL/XML

The following API's can be used to trasnform results of SQL Queries to XML:
JDBC
ODBC
SAX
DOM
XSLT
SQLXML

amen

If you want to show SQL Database's on WebPage, it needs to be in XML forma

no it doesn't. You could run some TSQL/SPs can back a result set and just print the values of the recordset using any scripting language. No need for XML at all.

You also don't need to use any APIs. MS SQL supports returning data from SQL Server in XML format which you could then just display on a page or transform using XSL to make it pretty.

XML is really just a fancy way of storing/transporting/exchanging data i.e instead of
[PHP]Test Test, 01/01/2000, Second Second, 02/02/2002[/PHP] you could store in xml as
[PHP]<MyXMLs><MyXML><><Name>Test Test</Name><DOB>01/01/2000</DOB></MyXML><MyXML><><Name>Second Second</Name><DOB>02/02/2002</DOB></MyXML>[/PHP]

this makes is easy to your to share data and for receipients to understand what you are sending. There are many industry standards for XML naming conventions.

XSL lets you transform that XML and modify how it is displayed. A common way is to use XSL to generate some HTML and display data for a particular user so using the above example User A might use A.xsl and only see the name while User B might use B.xsl and see the name/dob with both using the same xml data.

XSL is really a mini scripting language with for. if, loop, count statements etc.

BostonB

You could spit out XML from your SQL that you could transform into Excel documents. For reports, or people want to dopPivots on them.

amen

you are going to start export data to XML then to Excel I would say you are crazy.

Go look at MS Reporting Services that comes with MS SQL.
Allows you to generate reports which can be exported to Excel automatically. No need for XML.

As for Pivot tables better of having a report database with correct OLAP cubes or a least a table containing the report data and pivot that.

BostonB

We do all of the above depending on the context. But the xml to excel was a simple and robust way to generate excel data for power users who want that. Haven't been all that impressed with MS Reporting Services, seems a little bit clunky, especially for layout. 2005 anyway, perhaps 2008 version is better.