Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Other SQL Server Topics / December 2007

Tip: Looking for answers? Try searching our database.

Shred XML to SQL 2005

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Theo Müller - 26 Dec 2007 14:26 GMT
I am absolute a novice in XML but need to shred XML into SQL Server
2005.
I studied some examples, but only with simple XML Docs.
My docs seems to be looking more complex.

Maybe someone can point me into the right direction:

My XML looks like this:

<KINO_INFORMATION>
 <KINO_LIST>
   <KINO_LOKATION WONDER_KINO="9583718">
     <ADDRESS_INFORMATION>
        <CITY_ADDRESS>
           <SECTOR></SECTOR>
           <STREET>asdasdasd</STREET>
           <ZIP>12345</ZIP>
           <CITY>Somewhere</CITY>
           <COUNTRY>GERMANY</COUNTRY>
           <PHONE>555-555-555</PHONE>
           <GEO_COORDINATES FORMAT="WGS84">
             <LATITUDE>0</LATITUDE>
             <LONGITUDE>0</LONGITUDE>
           </GEO_COORDINATES>
           <ROUTE_DESCRIPTION>
             <TRANSPORTATION>Bus</TRANSPORTATION>
             <DESCRIPTION></DESCRIPTION>
           </ROUTE_DESCRIPTION>
           <ROUTE_DESCRIPTION>
             <TRANSPORTATION>Subway</TRANSPORTATION>
             <DESCRIPTION></DESCRIPTION>
           </ROUTE_DESCRIPTION>
        </CITY_ADDRESS>
     </ADDRESS_INFORMATION>
     <DESCRIPTIONS>
       <SHORT_DESCRIPTION></SHORT_DESCRIPTION>
     </DESCRIPTIONS>
     <ID>9583718</ID>
     <NAME>Metropolis</NAME>
     <KINO_TYP></KINO_TYP>
     <SAAL_LIST>
       <SAAL WONDER_ID="9583748">
         <NAME>Metropolis</NAME>
         <FEATURE></FEATURE>
         <SITZE>302</SITZE>
         <KINOTAG_LIST>
         </KINOTAG_LIST>
         <TONSYSTEM_LIST>
           <TONSYSTEM>Dolby SR</TONSYSTEM>
         </TONSYSTEM_LIST>
         <VORSTELLUNGS_LIST>
           <VORSTELLUNG TERMIN_ID="55138820">
             <FILM_ID>52015561</FILM_ID>
             <START>
               <DAY>12.12.07</DAY>
               <TIME>18:00:00</TIME>
             </START>
             <FASSUNG></FASSUNG>
             <DOUBLE_FEATURE FOLLOWED_BY="">N</DOUBLE_FEATURE>
             <ADD_INFO>12.12.2007</ADD_INFO>
           </VORSTELLUNG>
           <VORSTELLUNG TERMIN_ID="55138798">
             <FILM_ID>45408016</FILM_ID>
             <START>
               <DAY>12.12.07</DAY>
               <TIME>20:15:00</TIME>
             </START>
             <FASSUNG></FASSUNG>
             <DOUBLE_FEATURE FOLLOWED_BY="">N</DOUBLE_FEATURE>
             <ADD_INFO>12.12.2007</ADD_INFO>
           </VORSTELLUNG>
         </VORSTELLUNGS_LIST>
       </SAAL>
     </SAAL_LIST>
   </KINO_LOKATION>

 <FILM_LIST>
   <FILM>
     <NAME>Verwünscht</NAME>
     <FILM_ID>55268869</FILM_ID>
     <ORIGINAL_NAME>Enchanted</ORIGINAL_NAME>
     <DARSTELLER_LIST>
       <DARSTELLER>Amy Adams</DARSTELLER>
       <DARSTELLER>Patrick Dempsey</DARSTELLER>
       <DARSTELLER>James Marsden</DARSTELLER>
     </DARSTELLER_LIST>
     <REGIE_LIST>
       <REGIE>Kevin Lima</REGIE>
     </REGIE_LIST>
     <DREHBUCH_LIST>
       <DREHBUCH>Bill Kelly</DREHBUCH>
     </DREHBUCH_LIST>
     <LAENGE>108</LAENGE>
     <JAHR>2007</JAHR>
     <LAND>USA</LAND>
     <PREMIERE>n</PREMIERE>
     <DESCRIPTIONS>
       <LONG_DESCRIPTION>Die bildschöne Giselle wird kurz vor ihrer
Märchenhochzeit von ihrer Stiefmutter aus dem magischen
Zeichentrickreich Andalasia nach Manhattan verbannt. Hilflos und ganz
auf sich allein gestellt, läuft sie einem smarten Scheidungsanwalt in
die Arme, der sie bei sich aufnimmt. Als sich die beiden langsam
näherkommen, taucht Giselles Märchenprinz auf, um sie
zurückzuholen.</
LONG_DESCRIPTION>
       <SHORT_DESCRIPTION>Enchanted</SHORT_DESCRIPTION>
     </DESCRIPTIONS>
     <GENRE_LIST>
       <GENRE>Fantasy</GENRE>
       <GENRE>Liebesfilm</GENRE>
     </GENRE_LIST>
     <KLASSIFIZIERUNG>
       <TYP>FSK</TYP>
       <WERT>oA</WERT>
     </KLASSIFIZIERUNG>
     <KLASSIFIZIERUNG>
       <TYP>Kinderfilm</TYP>
       <WERT>n</WERT>
     </KLASSIFIZIERUNG>
   </FILM>
 </FILM_LIST>
</KINO_INFORMATION>
Erland Sommarskog - 26 Dec 2007 23:11 GMT
> I am absolute a novice in XML but need to shred XML into SQL Server
> 2005.
> I studied some examples, but only with simple XML Docs.
> My docs seems to be looking more complex.
>
> Maybe someone can point me into the right direction:

Since I don't know what you what you want to get out of the document,
I can't give you any code.

In general, there are two methods: OPENXML and XQuery. OPENXML is
the older method, introduced in SQL 2000, XQuery was added in SQL 2005.
XQuery is a standardised language to query XML documents, not only
in SQL Server.

OPENXML may be easier to get started with, but I since XQuery is newer,
I think it's better in the long run. Then again, I have seen situations
where the performance XQuery have been outright poor with element-
centred XML which is what you have.

Both OPENXML and XQuery are described in Books Online, and you could
start reading, but of course if you are completely new to XML, the
learning curve may be a bit steep.

Unfortunately, I cannot really give good recommendations on books. I
learnt XML myself from "XML Step by Step" by Michael Yuong and
"Programming Microsoft SQL Server 2000 with XML" by Graeme Malcolm.
The latter book is definitely dated by now, unless the author has an
update for SQL 2005. The former book is a little more timeless, and
is good enough to learn you what XML is, but it will not teach you
XQuery. Unless, again, there is a newer edition; my copy is several
years old.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Theo Müller - 28 Dec 2007 14:02 GMT
> > I am absolute a novice inXMLbut need toshredXMLintoSQLServer
> > 2005.
[quoted text clipped - 5 lines]
> Since I don't know what you what you want to get out of the document,
> I can't give you any code.

thanks for your reply.
quite easy speaking, i want to 'import' the xml to tables. But because
of the structure of the xml, i don't know how to do.
THere is a different sample in this group, with a 'easy' xml doc. i
understandn how to do it with that one....

so maybe its a bit more clear now.

BR
Erland Sommarskog - 28 Dec 2007 22:32 GMT
> thanks for your reply.
> quite easy speaking, i want to 'import' the xml to tables. But because
> of the structure of the xml, i don't know how to do.

And I don't know the tables, nor which elements you want there, so I
don't know either.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.