Hi Experts,
I have a table name LIBRARY
LIBRARY TABLE
LIBRARYKEY NAME LIBRARIAN MATERIALS
==========================
==========
======
1 A M. Smith (...)
2 B Z. Jones (...)
3 C M. James (...)
4 D R. Allen (...)
5 F K. Martin (...)
==========================
==========
======
Where
LIBRARYKEY is INT (primarykey)
NAME is varchar(30)
LIBRARIAN is varchar(30)
MATERIAL is TEXT
The MATERIAL field contains RAW XML data (document) that will list all
materials/stuffs available for a particular library.
Sample RAW XML document inside the MATERIAL field is below:
==========================
==========
======
<Materials>
<Magazine>
<Item>
<CODE>15-041</CODE>
<CATEGORY>International</C
ATEGORY>
<RELEASE_DATE>1999-03-07</
RELEASE_DA
TE>
<TITLE>Newsweek</TITLE>
<SALES>137853</SALES>
</Item>
<Item>
<CODE>15-048</CODE>
<CATEGORY>National</CATEGO
RY>
<RELEASE_DATE>1999-04-21</
RELEASE_DA
TE>
<TITLE>Showbiz Insider</TITLE>
<SALES>375398</SALES>
</Item>
<Item>
<CODE>15-105</CODE>
<CATEGORY>Local</CATEGORY>
<RELEASE_DATE>1999-05-10</
RELEASE_DA
TE>
<TITLE>Bisaya Weekly</TITLE>
<SALES>297312</SALES>
</Item>
</Magazine>
<Books>
<Item>
<CODE>16-041</CODE>
<CATEGORY>HTML</CATEGORY>
<RELEASE_DATE>1998-03-07</
RELEASE_DA
TE>
<TITLE>Instant HTML</TITLE>
<SALES>127853</SALES>
</Item>
<Item>
<CODE>16-048</CODE>
<CATEGORY>Scripting</CATEG
ORY>
<RELEASE_DATE>1998-04-21</
RELEASE_DA
TE>
<TITLE>Instant JavaScript</TITLE>
<SALES>375298</SALES>
</Item>
<Item>
<CODE>16-105</CODE>
<CATEGORY>ASP</CATEGORY>
<RELEASE_DATE>1998-05-10</
RELEASE_DA
TE>
<TITLE>Instant Active Server Pages</TITLE>
<SALES>297311</SALES>
</Item>
</Books>
</Materials>
==========================
==========
==
QUESTION:
Is there any way I can read contains of LIBRARY.MATERIALS field
(for all records on LIBRARY table) and store it to a #temptable whose records are
based on the XML document found on the LIBRARY.MATERIALS field?
The #temptable should also have a LIBRARYKEY field and will contain the
LIBRARY.LIBRARYKEY field.
I'm using MSSQL server 2000
Please Help,
jrmn
