Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P4

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

92
lượt xem 14
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'programming microsoft sql server 2000 with microsoft visual basic .net - p4', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Programming Microsoft SQL Server 2000 with Microsoft Visual Basic .Net - P4

a disabled t r igger, all y ou hav e t o do is enable it . Recall t hat you can disable and enable a t r igger w it h an ALTER TABLE st at em ent for t he t able w it h t he t rigger t hat y ou want t o disable t em porarily. The follow ing scr ipt dem onst rat es t he synt ax for cr eat ing a t rigger for t he MyTable t able cr eat ed earlier in t his chapt er. ( See t he “Cr eat ing a Scalar UDF Wit hout Param et ers” sect ion.) The t r igger pr ot ect s t he t able fr om insert s, updat es, and delet es by rolling back t he t ransact ion associat ed w it h t he t r igger. The script st art s by r em ov ing any previous version of t he t rgKeepMyTableUnt ouched t r igger and t hen begins a CREATE TRI GGER st at em ent . Lik e m ost ot her CREATE st at em ent s, t he CREATE TRI GGER st at em ent m ust occur at t he t op of a bat ch. Therefore, t he code t o drop t he old version ends wit h t he GO k eyw ord. The ON clause of t he CREATE TRI GGER st at em ent designat es t he MyTable t able as t he one t o w hich t he t r igger w ill belong. The FOR clause indicat es t hat t he t r igger w ill fir e for insert , updat e, and delet e ev ent s. The first st at em ent aft er t he AS k eyw ord is a RAI SERROR st at em ent t hat sends a cust om m essage back t o t he Messages pane of Query Analyzer. An inform at ional m essage issued from a t rigger is useful for let t ing a user k now t hat a t r igger fired. The RAI SERROR st at em ent can serv e ot her funct ions as well, but it is a robust alt er nat iv e t o t he PRI NT st at em ent for sending m essages t o t he Messages pane. The st r ing for a cust om m essage can be up t o 400 charact ers. The t railing values 16 and 1 indicat e t he sever it y and st at e for t he err or. For sim ple inform at ional m essages, y ou can consist ent ly apply t hese v alues. The second T- SQL st at em ent in t he script r olls back t he t ransact ion t o m odify t he t able. The ROLLBACK TRAN st at em ent is an abbrev iat ed version of t he ROLLBACK TRANSACTI ON st at em ent . I n eit her form , t his st at em ent rem ov es any insert ed rows, r est or es any colum n values t o t heir nonupdat ed st at e, and adds back any delet ed r ows. You w ill generally want t o use t he ROLLBACK TRAN st at em ent as t he last st at em ent in a t rigger because any st at em ent s aft er ROLLBACK TRAN can m odify t he t able for a t r igger. --trgKeepMyTableUntouched --Drop prior version of trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name = ’trgKeepMyTableUntouched’ AND type = ’TR’) DROP TRIGGER trgKeepMyTableUntouched GO --Create new trigger to keep MyTable table untouched. CREATE TRIGGER trgKeepMyTableUntouched ON MyTable FOR INSERT, UPDATE, DELETE AS RAISERROR(‘Message from trgKeepMyTableUntouched.’,16,1) ROLLBACK TRAN GO The follow ing scr ipt is a collect ion of T- SQL st at em ent s t hat dem onst rat es t he behav ior of t he t rigger as well as how t o disable and rest ore t he t r igger. The first couple of bat ches in t he script at t em pt t o delet e all r ows fr om t he My Table t able and m odify a colum n value in t he t able. Neit her bat ch succeeds because t he t rgKeepMyTableUnt ouched t r igger pr ot ect s t he MyTable t able fr om delet e and updat e ev ent s ( as w ell as insert ev ent s) . I f it becom es essent ial t o m odify a t able wit h a t rigger t hat blocks changes, y ou can t em porar ily disable t he t r igger. The script dem onst rat es t he synt ax for t he t rgKeepMyTableUnt ouched t r igger . You have t o m odify t he My Table t able w it h t he ALTER TABLE st at em ent t o disable it s t r igger. Aft er disabling t he t r igger, t he script changes t he m axim um v alue in t he col1 colum n. Then, in anot her bat ch, t he script r est or es t he init ial m ax im um value. The script s use a scalar UDF dev eloped ear lier in t his chapt er t o accom plish t hese t asks. Aft er successfully Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
m odify ing t he t able w it h t he t r igger disabled, t he script enables t he t rigger again for t he MyTable t able w it h t he ALTER TABLE st at em ent . Just t o confirm t he t rigger ’s operat ion, t he script again at t em pt s t o delet e all rows fr om t he t able. The t rigger fir es and pr int s it s inform at ional m essage and rolls back t he t ransact ion t o rem ov e t he r ows from t he t able. --Demo_trgKeepMyTableUntouched --An attempt to delete all records fails with --trigger error message. DELETE FROM MyTable GO --An attempt to update the maximum value in --col1 in the MyTable table fails also. UPDATE MyTable SET col1 = dbo.udfOneHigherThanMax() WHERE col1 = (SELECT MAX(col1) FROM MyTable) GO --Disable the trigger for MyTable without dropping it. ALTER TABLE MyTable Disable TRIGGER trgKeepMyTableUntouched GO --Update attempt for MyTable succeeds. UPDATE MyTable SET col1 = dbo.udfOneHigherThanMax() WHERE col1 = (SELECT MAX(col1) FROM MyTable) SELECT * FROM MyTable GO --Restoring update event also succeeds. UPDATE MyTable SET col1 = dbo.udfOneHigherThanMax() - 2 WHERE col1 = (SELECT MAX(col1) FROM MyTable) SELECT * FROM MyTable GO --Re-enable trigger. ALTER TABLE MyTable Enable TRIGGER trgKeepMyTableUntouched GO --An attempt to delete all records fails again --with trigger error message. DELETE FROM MyTable GO Ar ch ivin g Ch a n ge s t o a Ta ble The logical t ables insert ed and delet ed cont ain t he changes t hat users m ake t o a t able. Unfort unat ely, t he insert ed and delet ed t ables are available only for t he t im e t hat a t r igger has cont rol of an applicat ion. When t he t r igger closes, SQL Ser ver in effect clears t he t ables. I f y ou want t o persist som e subset of t he changes t o a t able for perm anent ready access, you can use t r iggers t o save t he cont ent s of t he logical insert ed and delet ed t ables t o a t able in a SQL Ser ver Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
dat abase. Because changes ( insert s, updat es, and delet es) affect t he insert ed and delet ed t ables different ly, one approach is t o cr eat e a separat e t r igger for each t ype of change. This sim plifies t he t rigger logic, and it m ak es each t ype of change run fast er t han hav ing one t rigger t hat decipher s t he t y pe of change and t hen archiv es t he insert ed and delet ed t ables properly. The follow ing scr ipt creat es t hr ee t r iggers t o log insert s, updat es, and delet es t o t he My Table t able in t he ChangeLogFor MyTable t able. The script st art s by rem ov ing t he t r gKeepMyTableUnt ouched t r igger creat ed in t he pr ev ious sam ple. Recall t hat t he pr ev ious t r igger block s all changes t o t he MyTable t able. Next t his procedur e cr eat es a fr esh blank version of t he ChangeLogForMy Table t able. The t able has four colum ns— one for t he col1 values from t he insert ed or delet ed t able, a second for t he t ype of change, a t hird for t he dat e and t im e of t he change, and a fourt h colum n for t he login of t he user m ak ing t he change. Aft er creat ing a t able t o archiv e changes, t he script creat es a fresh copy of t he t rgI nsert ToChangeLog t rigger . This t r igger copies t he col1 value from t he insert ed t able t o a local var iable. Then it uses t he local v ariable in t he VALUES clause of an I NSERT I NTO st at em ent t o persist t he new value t o t he ChangeLogFor MyTable t able. The script uses a st ring const ant — I NSERT—t o designat e t he t ype of change. The CURRENT_TI MESTAMP and SYSTEM_USER k ey words denot e built - in funct ions t hat r et ur n t he curr ent dat e and t im e as well as t he login for t he cur rent user ( t he one who m ak es t he change) . The CREATE TRI GGER st at em ent s for t he t r gDelet eToChangeLog and t rgUpdat eToChangeLog t riggers persist t he delet e and updat e col1 values t o t he ChangeLogForMy Table t able. When logging delet es, you use t he delet ed t able inst ead of t he insert ed t able. I n t he case of updat es, you log t he cont ent s of t he delet ed and insert ed t ables t o t he ChangeLogForMyTable t able. Howev er, t he basic design of delet e and updat e t r iggers cor responds t o t he t rgI nsert ToChangeLog t rigger . --trgInsertUpdateDeleteToChangeLog --Drop prior version of trgKeepMyTableUntouched trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name = ’trgKeepMyTableUntouched’ AND type = ’TR’) DROP TRIGGER trgKeepMyTableUntouched GO --Remove prior version of ChangeLogForMyTable table. IF EXISTS(SELECT TABLE_NAME = ’ChangeLogForMyTable’ FROM INFORMATION_SCHEMA.TABLES) DROP TABLE ChangeLogForMyTable --Create ChangeLogForMyTable table. CREATE TABLE ChangeLogForMyTable ( col1 int, type varchar (10), changedatetime datetime, changeuser varchar(128) ) GO --Drop prior version of trgInsertToChangeLog trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name = ’trgInsertToChangeLog’ AND type = ’TR’) DROP TRIGGER trgInsertToChangeLog GO --Create trigger to monitor inserts. CREATE TRIGGER trgInsertToChangeLog ON MyTable Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FOR INSERT AS DECLARE @col1value int SET @col1value = (SELECT col1 FROM inserted) INSERT INTO ChangeLogForMyTable VALUES(@col1value, ’INSERT’, CURRENT_TIMESTAMP, SYSTEM_USER) GO --Drop prior version of trgDeleteToChangeLog trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name = ’trgDeleteToChangeLog’ AND type = ’TR’) DROP TRIGGER trgDeleteToChangeLog GO --Create trigger to monitor deletes. CREATE TRIGGER trgDeleteToChangeLog ON MyTable FOR DELETE AS DECLARE @col1value int SET @col1value = (SELECT col1 FROM deleted) INSERT INTO ChangeLogForMyTable VALUES(@col1value, ’DELETE’, CURRENT_TIMESTAMP, SYSTEM_USER) GO --Drop prior version of trgUpdateToChangeLog trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name = ’trgUpdateToChangeLog’ AND type = ’TR’) DROP TRIGGER trgUpdateToChangeLog GO CREATE TRIGGER trgUpdateToChangeLog ON MyTable FOR UPDATE AS DECLARE @col1value int SET @col1value = (SELECT col1 FROM deleted) INSERT INTO ChangeLogForMyTable VALUES(@col1value, ’UPDATE’, CURRENT_TIMESTAMP, SYSTEM_USER) SET @col1value = (SELECT col1 FROM inserted) INSERT INTO ChangeLogForMyTable VALUES(@col1value, ’UPDATE’, CURRENT_TIMESTAMP, SYSTEM_USER) GO The follow ing scr ipt should be r un im m ediat ely aft er y ou creat e t he t r iggers w it h t he preceding scr ipt . I t also benefit s from a fresh copy of t he MyTable t able, such as t he one generat ed by t he udfHigher ThanMax script in t he “Cr eat ing a Scalar UDF Wit hout Param et er s” sect ion. The script m akes a ser ies of changes t o t he MyTable t able. Aft er each change, it uses SELECT st at em ent s t o ret urn t he MyTable t able and t he ChangeLogForMy Table t able. The first change is t o add a new row wit h t he value 25 for col1. Next it updat es t he value 25 t o 26. Finally it delet es t he r ow in t he MyTable t able w it h a col1 value of 26. --Demo_trgInsertUpdateDeleteToChangeLog --Insert a new row into MyTable and display --MyTable and ChangeLogForMyTable tables INSERT INTO MyTable (col1) VALUES (25) SELECT * FROM MyTable SELECT * Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FROM ChangeLogForMyTable GO --Update inserted row value and display --MyTable and ChangeLogForMyTable tables. UPDATE MyTable SET col1 = 26 WHERE col1 = 25 SELECT * FROM MyTable SELECT * FROM ChangeLogForMyTable GO --Delete updated row and display --MyTable and ChangeLogForMyTable tables. DELETE FROM MyTable WHERE col1 = 26 SELECT * FROM MyTable SELECT * FROM ChangeLogForMyTable GO Exam ining t he Result s pane cont ent s will allow you t o follow t he changes t o t he MyTable t able as well as t he ChangeLogForMy Table t able. The first display of t he ChangeLogForMy Table t able shows a t able w it h j ust one row and a col1 value of 25. I n t he next display of t he t able, y ou can see t hr ee r ows. This is because an updat e adds t w o rows t o t he t able. I n it s final appearance in t he result s pane, t he ChangeLogForMy Table t able cont ains four rows. En f or cin g a Busin e ss Rule on a Ta ble One of t he classic uses for t riggers is t he enforcem ent of business rules. Aft er all, t he t r igger always fir es befor e a change ev ent . The T- SQL in t he t r igger can assess t he change t o m ake sure it conform s t o business rules before com m it t ing t he change t o a t able. I f a change value doesn’t sat isfy a business rule, t he t rigger can t ak e an appropr iat e r em edy, such as r ej ect ing t he change or r ev ising t he change and inform ing t he user of any r em edial act ion. The next sam ple enforces a sim ple business r ule. The rule is t hat users can insert only even num bers int o col1 of t he My Table t able. Your norm al business rules can be subst ant ially m or e sophist icat ed t han t his sam ple, but t he t r iggers t o enforce t hose r ules can st ill use t he sam e logic. First you t est t he change value t o m ak e sure it adheres t o t he r ule. Second, if t he change value doesn’t confor m t o t he business r ule, y our t r igger can perform an appr opriat e r em edial act ion for t he invalid change value. Third, if t he change value sat isfies t he business rule, y ou insert it int o t he t able. N ot e Befor e running t he sam ple script in t his sect ion, m ake sure you drop all ot her t r iggers for t he MyTable t able t hat can conflict wit h t he sam ple below. The sam ple script on t he book ’s com panion CD r em oves all prior t rigger s cr eat ed for Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
t he MyTable t able in t his chapt er. For br ev it y, t he list ing here doesn’t show t he code for dropping all t hese t riggers. The sam ple uses an I NSTEAD OF t r igger. Because t his t ype of t r igger fir es before t he change ev ent , t her e is no need t o r oll back a t ransact ion for an inv alid act ion. The sam ple uses t he m odulo operat or ( % ) t o check whet her a num ber div ides ev enly by 2. A rem ainder of 1 indicat es an odd num ber. This out com e calls for a rem edial act ion. The act ion in t his inst ance is t o add 1 t o t he input value from t he insert ed t able, const ruct a m essage indicat ing t he alt ernat iv e act ion t ak en, and finally insert t he new ev en num ber int o t he t able. A rem ainder of 0 indicat es an ev en num ber . Because ev en num bers sat isfy t he business r ule, t he t r igger can j ust insert t he value from t he insert ed t able int o col1 of t he My Table t able. Aft er t he cr eat ion of t he t r igger, t he script includes dat a m anipulat ion and SELECT st at em ent s t o t est t he t rigger ’s logic. You can run t he sam ple script and see t he t rigger aut om at ically add 1 when t he script at t em pt s t o input an odd num ber ( 25) int o col1 in t he My Table t able. On t he ot her hand, t he t r igger m erely accept s t he insert of an even num ber ( 24) int o col1 in t he MyTable t able. --trgInsteadOfInsert --Drop prior version of trgInsteadOfInsert trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name = ’trgInsteadOfInsert’ AND type = ’TR’) DROP TRIGGER trgInsteadOfInsert GO --Create an INSTEAD OF trigger. CREATE TRIGGER trgInsteadOfInsert ON MyTable INSTEAD OF INSERT AS DECLARE @col1value int DECLARE @newcol1value int DECLARE @strMsg varchar(400) SET @col1value = (SELECT col1 FROM inserted) --If inserted value is odd, make it even --before inserting it. IF @col1value%2 = 1 BEGIN SET @newcol1value = @col1value + 1 SET @strMsg = ’The value you want to insert is: ’ + CAST(@col1value AS varchar(3)) + ’, but it violates a business rule.’ + CHAR(10) + ’ Therefore, I insert ’ + CAST(@newcol1value AS varchar(3)) + ’.’ RAISERROR (@strMsg,16,1) INSERT INTO MyTable (col1) VALUES(@newcol1value) END ELSE INSERT INTO MyTable (col1) VALUES(@col1value) GO --Try to insert an odd value into col1 in MyTable. INSERT INTO MyTable (col1) VALUES(25) --Display the col1 values in MyTable. SELECT * FROM MyTable --Delete the next even value after the odd value. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DELETE FROM MyTable WHERE col1 = 26 --Display the col1 values in MyTable. SELECT * FROM MyTable --Insert an even value into col1 in MyTable. INSERT INTO MyTable (col1) VALUES(24) --Display the col1 values in MyTable. SELECT * FROM MyTable --Delete the new even col1 value in MyTable. DELETE FROM MyTable WHERE col1 = 24 --Display the col1 values in MyTable. SELECT * FROM MyTable En f or cin g a Busin e ss Rule on a Vie w Two of t he advant ages of views are t hat t hey perm it you t o insulat e your dat abase schem a from t he user int erface for an applicat ion and t hat you can select iv ely expose subset s from a t able w it hout exposing all t he dat a in a base t able. These feat ur es perm it y ou t o secur e t he base t able or t ables for a view from all or m ost users while y ou grant t hese sam e users access t o a subset of t he dat a fr om t he base t able or t ables t hrough a view. Unfort unat ely, AFTER t r iggers never applied t o v iews, so prev iously y ou couldn’t enforce business rules wit h t riggers for v iews. SQL Ser ver 2000 int r oduced I NSTEAD OF t r iggers, w hich apply t o v iews. Ther efor e, you can gain t he benefit s of ex posing dat a t hr ough views and st ill be able t o enforce business r ules v ia t r igger s. The sam ple in t his sect ion dem onst rat es t he sy nt ax for apply ing a business r ule for insert s int o a view . The v iew is vewMy Table. This v iew ret urns all t he rows for t he colum n in t he MyTable t able. The business rule is t hat t he insert ed col1 v alue can be only 1 gr eat er t han t he cur r ent m ax im um in col1 of t he MyTable t able. N ot e As wit h t he sam ple script fr om t he pr eceding sect ion, you should rem ove all t r igger s t hat can conflict wit h t he new t r igger. The version of t he follow ing sam ple on t he book ’s com panion CD rem oves all prior t riggers creat ed for t he My Table t able in t his chapt er. For br ev it y, t he list ing her e doesn’t show t he code for dropping all t hese t riggers. The script below st art s wit h t he cr eat ion of t he vewMyTable v iew. Then t he script m ov es on t o creat e a fr esh v ersion of t rgI nst eadOfI nsert Forv ewMyTable. No special act ion is necessary for cr eat ing a t r igger for a v iew. I n t he ON clause for t he CREATE TRI GGER st at em ent , j ust nam e t he view— vew MyTable, in t his case. The t rigger ’s logic uses t he udfOneHigherThanMax UDF cr eat ed ear lier in t his chapt er . You should r un t he code t o cr eat e t his UDF if it isn’t available. The logic for enforcing t he business rule is t he sam e as for t he pr ev ious t r igger, alt hough Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
t he act ual business r ule is differ ent . An I F…ELSE st at em ent t est s for t he validit y of t he new value r elat iv e t o t he business rule. I f t he new value fails t he t est , t he t rigger perform s a r em edial act ion. This act ion print s a m essage let t ing t he user know t he new value is invalid. Because t he t r igger is an I NSTEAD OF t r igger, t here is no need t o r oll back t he insert . I f t he new v alue is valid, t he t r igger insert s t he new value int o v ewMy Table. Aft er t he script creat es t he t r igger, t he script goes on t o t est t he t r igger by t ry ing t o insert t w o new values. The first value v iolat es t he business rule, and t he t rigger r ej ect s it . The second value sat isfies t he business rule, and t he t rigger insert s t he new value int o col1 of t he MyTable t able. The final dat a m anipulat ion st at em ent in t he script rem oves t he value new ly insert ed int o t he v ewMyTable view t o rest ore t he base t able t o it s init ial st at e. --trgInsteadOfInsertForvewMyTable --Drop prior version of vewMyTable view. IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = ’vewMyTable’) DROP VIEW vewMyTable GO --Create vewMyTable view. CREATE VIEW vewMyTable AS SELECT * FROM MyTable GO --Drop prior version of trgInsteadOfInsertForvewMyTable trigger. IF EXISTS (SELECT name FROM sysobjects WHERE name = ’trgInsteadOfInsertForvewMyTable’ AND type = ’TR ’) DROP TRIGGER trgInsteadOfInsertForvewMyTable GO --Create an INSTEAD OF trigger for a view. CREATE TRIGGER trgInsteadOfInsertForvewMyTable ON vewMyTable INSTEAD OF INSERT AS DECLARE @col1value int SET @col1value = (SELECT col1 FROM inserted) IF @col1value > dbo.udfOneHigherThanMax() RAISERROR(‘Value too high.’,17,1) ELSE INSERT INTO vewMyTable (col1) VALUES(@col1value) GO --Attempting to insert a value of 100 fails --through vewMyTable. INSERT INTO vewMyTable (col1) VALUES(100) SELECT * FROM vewMyTable GO --Attempting to insert a value one higher --than the maximum value succeeds. INSERT INTO vewMyTable (col1) VALUES(dbo.udfOneHigherThanMax()) SELECT * FROM vewMyTable GO Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
--Remove inserted value. DELETE FROM vewMyTable WHERE col1 = dbo.udfOneHigherThanMax()-1 GO Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Cha pt e r 6 . SQL Se r ve r 2 0 0 0 XM L Fun ct iona lit y When Microsoft SQL Ser ver 2000 was launched, Microsoft com m it t ed it self t o prov iding t he best Ext ensible Mark up Language ( XML) funct ionalit y possible. XML is im port ant because it prom ises t o r ev olut ionize t he way dat abase and Web dev elopers im plem ent dat a access and dat a m anipulat ion capabilit ies in t heir solut ions. Microsoft said it would r ev ise t he init ial release wit h t im ely updat es t hat included new funct ionalit y r eflect ing t he rapidly ev olv ing XML st andards and relat ed developm ent issues. As t his chapt er was being pr epared, Micr osoft delivered on it s com m it m ent w it h t he r elease of it s lat est updat e— t he Microsoft SQL Ser ver 2000 Web Serv ices Toolk it . The t oolk it follows t wo ear lier r eleases: XML for SQL Server 2000 Web Release 1 and XML for SQL Ser ver 2000 Web Release 2. The Web Ser v ices Toolk it is based on SQLXML 3.0 and includes t he SQLXML 3.0 inst allat ion package. Microsoft says t hat t he feat ur es int roduced in SQLXML 1.0 and SQLXML 2.0 ar e included in t he SQLXML 3.0 package. See Chapt er 12 for coverage of t he com pat ibilit y of t he t oolk it w it h t he t wo prior Web r eleases. I n addit ion, see Ch a pt e r 1 3 for com m ent ary and sam ples using t he Web Ser v ices Toolk it . You will gain from t his chapt er an ov erall under st anding of XML funct ionalit y in SQL Ser ver w it h an em phasis on access t o t hat funct ionalit y v ia T- SQL, XML schem as and t em plat es, and hypert ext t ranspor t prot ocol ( HTTP) . Chapt er 12 w ill refocus on XML so t hat you can build on t he underst anding pr esent ed her e w hile you lear n how t o t ap t he XML capabilit ies in SQL Serv er w it h Visual Basic .NET and relat ed t echnologies, such as ADO.NET. Wit h XML, dev elopers can build incredibly pow er ful solut ions for r et r iev ing and m aint aining dat a ov er Web connect ions. As t he w ord get s out about how easy it is t o creat e t hese solut ions, you w ill becom e an evangelist for using XML wit h SQL Ser ver. This chapt er relies on t he Nort hw ind sam ple dat abase. The chapt er sam ples add a couple of new v iews and user- defined funct ions t o t he dat abase for use wit h XML files. T- SQL script s for creat ing t hese obj ect s ar e included wit h t he sam ple files for t his chapt er. The m ain r esource for t he chapt er is a collect ion of nearly 20 XML files along w it h an assort m ent of URLs. Som e of t he URLs dem onst rat e direct access t o a SQL Server dat abase, while ot her URLs inv ok e an XML file and access a SQL Serv er dat abase indir ect ly t hrough t he XML file. Ove r view of XM L Su ppor t I n lear ning about XML funct ionalit y, it is im port ant t o r ecall t hat Micr osoft int r oduced XML pr ocessing power t o SQL Ser ver 2000 in m ult iple wav es. This m eans t hat select ed XML feat ures available fr om t he init ial version of SQL Ser ver 2000 hav e been obsolet ed, or at least depr ecat ed, by subsequent ly int r oduced XML t echniques. This is because Web Release 1 and Web Release 2— and now t he Web Ser v ices Toolk it — added new XML funct ionalit y not available in t he init ial release. The ov erv iew of XML capabilit ies in t his sect ion has t wo part s. First it briefly sum m ar izes im port ant XML feat ur es for t he init ial release of SQL Serv er 2000 and each of t he first t wo Web releases. Second it pr ov ides helpful inform at ion for inst alling t he Web releases. See Chapt er 12 and Ch a pt e r 1 3 for m ore Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
inform at ion about t he lat est Web release, t he Microsoft SQL Ser ver 2000 Web Ser vices Toolk it . Su m m a r y of XM L Fe a t u r e s by SQL Se r ve r Re le a se The init ial r elease of SQL Serv er 2000 offered XML funct ionalit y in four m ain areas. • The abilit y t o access SQL Serv er v ia HTTP. This for m of access relies on t he creat ion of a Micr osoft I nt ernet I nform at ion Ser v ices ( I I S) v irt ual dir ect or y for each dat abase for w hich you prov ide access via HTTP. • Support for XDR ( XML- Dat a Reduced) schem as. You can use t hese schem as t o creat e XML- based v iews of SQL Ser ver r ow sources, and y ou can use a subset of t he XML Pat h ( XPat h) query language t o query t hese views. The full XPat h specificat ion is a Wor ld Wide Web Consort ium ( W3C) st andard ( as out lined at ht t p: / / w ww.w3.org/ TR/ xpat h) . • Ret r iev ing and wr it ing XML dat a. Wit h t he FOR XML clause for t he T- SQL SELECT st at em ent , SQL Ser ver prov ides a rout e for reading it s dat a sources and ret ur ning result set s in XML form at . OPENXML is a new funct ion t hat can r et ur n a r owset based on an XML docum ent . Because T- SQL enables t he use of t he OPENXML funct ion in a m anner sim ilar t o t hat of t he OPENROWSET funct ion, y ou can use I NSERT st at em ent s t o populat e dat a sources based on XML docum ent cont ent s. • Enhancem ent s for XML t o Microsoft SQL Server 2000 OLE DB provider ( SQLOLEDB) . These XML im pr ov em ent s com e along wit h version 2.6 of Micr osoft Dat a Access Com ponent s. Using t he new capabilit ies perm it s you t o pass XML- form at t ed dat a t o a Com m and obj ect and ret urn XML- for m at t ed dat a fr om a Com m and obj ect . I n eit her case, t he dat a passes as a St ream obj ect . Web Release 1 was last updat ed on Febr uar y 15, 2001. This r elease adds select ed new XML capabilit ies t o t he XML feat ures int r oduced w hen SQL Serv er 2000 init ially shipped in t he fourt h quart er of 2000. As y ou can see, Microsoft wast ed no t im e enhancing t he init ial capabilit ies. Web Release 1 creat es t wo m aj or im pr ov em ent s along w it h a collect ion of m inor ones. • Updat egram s enable t ransact ion- based dat a m anipulat ion using XML. Updat egram s offer an XML- based synt ax for insert ing, updat ing, and delet ing r ecords in a SQL Serv er r ow source. You can specify t ransact ions for set s of operat ions w it hin Updat egram s so t hat all t he dat a m anipulat ion t asks wit hin a t ransact ion occur or none occur. By using Updat egram s inst ead of t he OPENXML funct ion, dev elopers can im pr ov e t he perform ance of t heir insert s, updat es, and delet es w hile sim plify ing t he coding. • XML Bulk Load t arget s m ov ing m assiv e am ount s of XML- based dat a int o SQL Ser ver. This feat ure addresses t he needs of dat abase adm inist rat ors and ot hers w ho r egular ly use eit her t he BULK I NSERT st at em ent or t he bcp ut ilit y. I n a non- t ransact ion- based m ode, y ou can insert XML- for m at t ed dat a fast er t han w it h Updat egram s or t he OPENXML funct ion. • Select ed ot her Web Release 1 enhancem ent s. New sy nt ax offers you t he abilit y t o specify w it h a param et er t he ret urn of binar y dat a fr om a SQL Ser ver dat a source. Virt ual direct or y m anagem ent t ools expand t o offer m or e precise cont r ol ov er how users can access a dat abase v ia a v irt ual dir ect or y. Synt ax enhancem ent s im pr ov e your abilit y t o m ap XML schem as t o SQL Serv er dat a sour ces and generally m anage XML t em plat es. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Web Release 2 cont inued t he pat t ern of int er m ediat e r eleases t hat enhance t he XML funct ionalit y of SQL Serv er 2000. The last updat e for Web Release 2 was Oct ober 15, 2001, eight m ont hs aft er Web Release 1. I n I nt er net t im e, t his gap is long enough for a m aj or upgr ade— and Microsoft t ook advant age of t he int erval t o offer significant new funct ionalit y . Web Release 2 is especially appropr iat e for t hose planning t o dev elop solut ions w it h a .NET language, such as Visual Basic .NET. I highlight four m aj or areas of XML funct ionalit y and operat ion associat ed wit h Web Release 2: • Com pliance w it h t he W3C schem a specificat ion k now n as XML Schem a Definit ion ( XSD) . While t his release doesn’t drop support for t he propriet ary XDR schem a specificat ion, Microsoft adds new funct ionalit y t hat is com pliant only w it h t he indust ry - st andard XSD. Adopt ing XSD schem as in y our own work w ill ensur e t he int eroperabilit y of y our applicat ions w it h t hose of ot hers who subscribe t o t he XSD specificat ion. • Client - side form at t ing perm it s t he XML form at t ing of SQL Serv er r owset s on t he I I S ser ver rat her t han t he dat abase serv er. This offers pot ent ial scalabilit y advant ages because m ult iple v irt ual dir ect or ies from differ ent I I S serv ers can point t o t he sam e dat abase on a dat abase serv er. I n addit ion, client - side for m at t ing r em oves processing from a dat abase serv er t hat m ight hav e ot her pr ocessing requirem ent s besides t hose for one or m or e I I S serv ers. • Two new dat a access com ponent s enhance XML processing capabilit ies. First , t he SQLXMLOLEDB prov ider facilit at es m ult iple obj ect iv es, including client - side form at t ing and Act iv eX Dat a Obj ect s ( ADO) access t o Web Release 2 funct ionalit y. SQLXMLOLEDB isn’t a dat a prov ider; y ou use it in com binat ion w it h SQLOLEDB, t he SQL Serv er ADO dat a prov ider. Second, SQLXML Managed Classes explicit ly expose t he Web Release 2 obj ect m odel, SQLXML 2.0, t o t he .NET Fram ework . By using t hese m anaged classes, Visual Basic .NET dev elopers can apply DiffGram s as an alt er nat ive t o Updat egr am s for dat a m anipulat ion t asks. • Side- by- side inst allat ion allows Web Release 2 t o r un on t he sam e m achine w it h Web Release 1. When using Web Release 2 in t his fashion, dev elopers need t o explicit ly r efer ence t he version t hey need for t heir applicat ions. For ex am ple, client - side pr ocessing is exclusiv ely available from v irt ual direct or ies com pliant w it h Web Release 2. Sim ilarly, t he XML Bulk Loading capabilit y is dependent on Web release. Each Web r elease has it s own dist inct DLL for im plem ent ing t he XML Bulk Loading feat ur e. You m ust regist er t he one t hat your applicat ion r equires. W e b Re le a se I n st a lla t ion Bot h Web Release 1 and Web Release 2 ar e fully support ed r eleases for SQL Ser ver 2000. These r eleases shouldn’t be confused wit h serv ice packs t hat fix problem s. While a Web release can r em edy a problem , it s m ain goal is t o add new funct ionalit y not pr esent in an ear lier release. I n order t o inst all Web Release 1 or 2, your com put er m ust hav e inst alled SQL Ser ver 2000 RTM ( Version 8.00.194) . You can obt ain t he Web r eleases from ht t p: / / w ww.m icrosoft .com / sql/ dow nloads/ . Click t he link labeled XML For SQL Server Web Release 1 ( WR1) for Web Release 1. Click t he link labeled XML For SQL Serv er Web Release 2 ( SQLXML 2.0) for Web Release 2. You w ill t ypically be downloading t he r eleases t o a com put er equipped w it h an I I S serv er. Therefor e, you should t ak e t he norm al pr ecaut ions t o guard against acquir ing a v irus during your I nt er net connect ion t im e. Aft er y ou com plet e inst alling a Web release, y our Pr ogr am s m enu is updat ed w it h an it em for t he release. Web Release 1 adds a new m enu it em labeled Micr osoft Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
SQL Ser ver XML Tools, which includes a single it em — XML For SQL Docum ent at ion. This it em opens t he Soft war e Dev elopm ent Kit ( SDK) for t he package, w hich includes docum ent at ion on t he feat ures of t he r elease. Web Release 2 adds SQLXML 2.0 t o t he Pr ogram s m enu. The SQLXML 2.0 m enu cont ains t hr ee it em s: Configure I I S, SQL 2.0 Docum ent at ion, and SQLXML 2.0 Readm e. Configure I I S offers a new wizard for configur ing a v irt ual dir ect ory t o int eract wit h SQL Serv er ( updat ed fr om t he wizard in t he init ial r elease) . The inst allat ion of Web Release 2 also offers a new SQLI SAPI filt er and SQLXML DLL files for t he m iddle t ier t hat replace t he v ersions shipping w it h t he init ial r elease of SQL Serv er 2000. Cr eat ing a v irt ual direct or y w it h t he Configure I I S m enu it em perm it s y our applicat ions t o t ak e advant age of t he new feat ures enabled by t hese com ponent s. You can also use t he new w izard t o upgrade v irt ual dir ect ories t o t ake adv ant age of feat ures int r oduced w it h Web Release 2. I nst alling t he files for Web Release 2 doesn’t cause t he rem oval or ov erwr it ing of t he files for Web Release 1. I t is t his feat ur e t hat perm it s y ou t o t ap t he feat ures of eit her r elease side by side on t he sam e com put er. XM L For m a t s a n d Sch e m a s XML is a rich and deep t echnology t hat pr om ises t o adv ance com put ing in t he first decade of t he t w ent y- first cent ur y as m uch as or m ore t han Visual Basic did in t he last decade of t he t went iet h cent ury . This sect ion deliv ers an int roduct ion t o XML- form at t ed dat a t hat part icular ly t arget s curr ent and pot ent ial applicat ions of XML w it h SQL Serv er. Subsequent sect ions will highlight how t o use XML w it h SQL Ser ver 2000; t his sect ion focuses on t hree XML t opics t hat w ill equip you t o underst and t he m at erial in t hose lat er sect ions. First I st art by descr ibing t he ov erall sy nt ax for XML docum ent s. Second I present t he basics of XML schem as as a dev ice for validat ing XML docum ent s. Third I rev iew XML annot at ed schem as as a m eans of creat ing a v iew for a SQL Ser ver dat a source. XM L D ocum e n t s XML is especially well suit ed for r epr esent ing st r uct ur ed docum ent s, such as inv oices and row sources in a dat abase. There is an im m ense body of lit erat ur e about XML. Aside from t his sect ion, one place t o st art fam iliar izing yourself wit h XML conv ent ions for represent ing dat a is t he Wor ld Wide Web Consort ium ( W3C) sit e at ht t p: / / w ww.w3c.org/ XML. This sit e cont ains link s t o m any valuable XML resources, such as t he W3C Recom m endat ion for XML 1.0. I n addit ion, t her e are m any XML- based t echnologies, such as XML Schem a, XPat h, XSL, and XSLT. Links at t he W3C m ain Web sit e can ser ve as a st art ing point for learning about t hese r elat ed t echnologies. A t y pical XML docum ent can r epresent dat a w it h a collect ion of t ags and a st art ing declarat ion. These t ags ar e sim ilar in som e ways t o HTML t ags, but t hey differ in im port ant ways. XML t ags denot e dat a elem ent s inst ead of how t o form at dat a. HTML assigns a pr ecise m eaning t o t ags. For exam ple, t he < p> t ag m eans st art a new paragraph. XML, on t he ot her hand, doesn’t assign a predet erm ined m eaning t o a t ag. I ndeed, t he sam e t ag can hav e a differ ent m eaning in differ ent XML docum ent s, and it is even possible for one t ag t o hav e different m eanings in t he sam e XML docum ent . By using nam espaces, developers can resolv e pot ent ial conflict s when t he sam e t ag has t w o or m ore different m eanings in t he sam e docum ent . XML lit erat ur e t ypically refers t o t he t ags in a docum ent as elem ent s. An elem ent can cont ain ot her elem ent s, a dat a v alue, or bot h ot her elem ent s and a dat a Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
value. Elem ent s can have parent , child, and sibling r elat ionships wit h one anot her. When an elem ent cont ains anot her one, t he cont ainer elem ent is t he parent elem ent and t he cont ained elem ent is t he child elem ent . For exam ple, < ShipperI D> , < Com panyNam e> , and < Phone> t ags can be child t ags of a par ent t ag < Shippers> in an XML docum ent w it h dat a for t he Shippers t able. The t ags bet ween a part icular inst ance of < Shippers> and < / Shippers> can denot e a row in t he Shippers t able. XML docum ent s t hat cont ain m ult iple occurr ences of at least one t ag, such as < Shippers> , m ust hav e one t ag set t hat cont ains all ot her t ags, such as < root > and < / root > . This out erm ost t ag set can occur j ust once wit hin an XML docum ent . An XML t ag ( or elem ent ) can have one or m ore at t ribut es. The use of at t ribut es is opt ional. At t ribut es appear w it hin t he t ag for an elem ent , such as < Shippers> . You designat e at t ribut es wit h nam e- value pairs. The nam e denot es t he at t ribut e’s nam e, and t he value depict s it s dat a value. Dat a values can appear in eit her single or double quot at ion m ar ks follow ing an equal sign behind t he at t ribut e nam e. You can r epr esent t he dat a for a t able w it h elem ent values, at t ribut e values, or bot h. The follow ing docum ent depict s t he Shippers t able dat a from t he Nort hwind dat abase repr esent ed w it h elem ent s and no at t r ibut es. Not ice t hat t he first set of angle bracket s ( < > ) declar es t he docum ent as an XML docum ent in ver sion 1 for m at . The ut f- 8 designat ion for encoding denot es a conv ent ion for conv ert ing charact ers t o bit sequences inside a com put er. Because t he < Shippers> t ag is repeat ed t hree t im es in t he docum ent , a par ent t ag set t hat appears j ust once is necessary ; t he < root > and < / root > t ags m eet t his requir em ent . The nam e root has no special m eaning; any ot her legit im at e nam e for a t ag, such as ShippersRoot , can replace r oot . The < Shippers> t ag is t he par ent of t he < ShipperI D> , < Com panyNam e> , and < Phone> t ags. These lat t er t hr ee t ags are siblings of one anot her. 1 Speedy Express (503) 555-9831 2 United Package (503) 555-3199 3 Federal Shipping (503) 555-9931 The next XML docum ent shows t he sam e dat a fr om t he Shippers t able as t he preceding one. I n t his inst ance, t he docum ent ’s form at t ing repr esent s dat a values wit h at t r ibut es inst ead of elem ent s or t ags. The declar at ion for t his XML docum ent inst ance is t he sam e as in t he pr eceding sam ple. At t r ibut es appear in a pair ed arr angem ent — fir st t he at t r ibut e nam e followed by an equal sign, and second t he at t r ibut e v alue in double quot at ion m arks. The colum n values for each row in t he Shippers t able appear wit hin a separ at e < Shippers> t ag in t he docum ent . The t railing / charact er wit hin each < Shippers> t ag is an alt er nat iv e t o designat ing < / Shippers> t o close t he < Shipper s> t ag. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figur e 6- 1 shows t he Shippers t able in t he XML form at for each of t he preceding XML docum ent files. The figur e reveals how t he XML appears w it hin a browser. Not ice t hat y ou can read t he dat a! Many ot her dat a form at s don’t appear so readable in a browser. XML’s charact er - based form at for represent ing dat a is one of t he advant ages of XML over ot her form at s for r epr esent ing dat a. I n t he browser view of shippers_elem ent s.xm l, you can collapse t he dat a for any indiv idual r ow in t he Shippers t able by click ing t he m inus sign ( - ) next t o t he opening < Shippers> t ag for a row. You can collapse t he dat a for all t hr ee rows by click ing t he m inus sign next t o t he opening < root > t ag for eit her docum ent . Figu r e 6 - 1 . A p air of scr ee n sh ot s illu st ra t in g t h a t u ser s ca n re a d ily e x a m in e t h e con t e n t s of a n X M L docu m e n t in a b row se r. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
XM L Sche m a s An XML schem a pr ov ides a fram ework for describing t he st r uct ur e and v alidat ing t he cont ent s of an XML docum ent . Wit h an XML schem a, you can k now what values ar e legit im at e for any t ag or at t ribut e inst ance. You can also use schem as t o place const raint s on t he range of accept able values for a dat a elem ent . By specify ing cardinalit y for elem ent s wit h t he m inOccurs and m ax Occurs elem ent at t ribut es, you can specify how m any elem ent inst ances ar e legit im at e in an XML docum ent . You can addit ionally designat e w het her an elem ent has any at t ribut es, and t he relat ionships am ong elem ent s. The W3C appr ov ed on May 2, 2001, a r ecom m endat ion ( ht t p: / / www .w3.org/ 2001/ XMLSchem a) t hat serv es as t he indust ry st andard for expressing XML schem as. Dev elopers refer t o t he W3C schem a st andar d as an XSD schem a. St art ing w it h Web Release 2, SQL Ser ver adopt ed t his st andard. Befor e Web Release 2, SQL Ser ver work ed w it h XDR schem as— a precursor of t he XSD schem a. This chapt er uses exclusively XSD schem as. The follow ing scr ipt r epr esent s t he shell for a schem a. Not ice t hat an XSD schem a is an XML docum ent because it st art s w it h an XML declarat ion. This m eans t hat you can describe an XSD schem a w it h t he sam e synt ax t hat y ou use for any XML docum ent . I n addit ion, not ice t he reference t o t he nam espace at ht t p: / / www .w3.org/ XMLSchem a. This nam espace defines a set of t ags and at t ribut es for defining schem as as XML docum ent s. The xsd designat ion for t he nam espace is arbit rar y. ( For exam ple, you can use xs inst ead.) An XSD schem a can have m ore t han one nam espace r eference. Each nam espace can refer ence a different set of t ags and at t ribut es. By using a dist inct nam espace designat or for each nam espace, y ou can resolv e conflict s for ident ically nam ed t ags and at t ribut es bet ween t wo different nam espaces. The shell r efers t o t he t ags and at t ribut es wit h t he xsd nam espace designat ion. The schem a t ag or elem ent , which m arks t he beginning and end of a schem a, is fr om t he nam espace designat ed by xsd. ... A basic underst anding of sev eral form at t ing convent ions can help you get st art ed wr it ing your own schem a ( or at least equip you t o r ead t hose w rit t en by ot hers) . Elem ent declarat ions can be for a sim ple or a com plex t ype. A com plex t y pe elem ent has at least one child elem ent or one at t ribut e. You can ex plicit ly define a child elem ent w it hin a parent elem ent or refer t o a child elem ent defined elsewhere wit hin a schem a. A sim ple t ype elem ent has neit her a child elem ent nor an at t ribut e. I n addit ion, t he declarat ion for a sim ple t ype elem ent classifies t he dat a t ype for t he elem ent according t o one of t he built - in XSD dat a t y pes. The XSD dat a t ypes generally cor respond t o SQL Serv er dat a t y pes. See t he “Dat a Ty pe Coercions and t he sql: dat at ype Annot at ion” t opic in t he online docum ent at ion for Web Release 2 for a det ailed discussion of t he sim ilar it ies and differences bet w een SQL Serv er and XSD dat a t ypes. I n addit ion t o elem ent s, a schem a can also specify at t ribut es. According t o t he W3C convent ion, t he at t ribut es for a com plex t ype elem ent are designat ed follow ing t he specificat ions for or refer ences t o any child elem ent s. The follow ing XML docum ent is t he XSD schem a for t he shippers_elem ent s.xm l docum ent file present ed in t he preceding sect ion. Follow ing t he schem a t ag wit h t he nam espace declarat ion, t he schem a declar es a com plex elem ent t y pe for t he root t ag. The root elem ent is com plex because it has child elem ent s, nam ely, one or m ore Shippers elem ent s. The exact upper lim it for t he num ber of Shippers elem ent s wit hin t he root elem ent is unbounded. ( See t he assignm ent for m axOccurs.) The m inOccurs at t ribut e for t he choice specificat ion doesn’t appear Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
in t he schem a, but it s default value is 1. Ther efor e, t o allow an XML docum ent wit h no Shippers elem ent s, designat e t he value 0 for m inOccurs. Not ice t hat t he Shippers elem ent doesn’t appear nest ed w it hin t he r oot elem ent declar at ion. I nst ead, t he r oot elem ent declarat ion uses t he ref at t r ibut e t o refer t o t he Shippers elem ent . The Shippers elem ent declarat ion follows t he r oot elem ent declarat ion. The Shippers elem ent has t hree child elem ent s— ShipperI D, Com pany Nam e, and Phone. I n t he following schem a, t he declarat ions for t he child elem ent s appear nest ed w it hin t he Shippers elem ent . Each child elem ent has a dat a t y pe derived from an XSD built - in dat a t ype, such as t he int eger or st ring dat a t ype. The rest r ict ion elem ent in t he child declarat ions denot es t he dat a t ype for t he child elem ent s fr om t he built - in dat a t ype. I n t he case of t he ShipperI D elem ent , t he declarat ion lim it s t he elem ent ’s values t o int egers. I n t he case of t he Com panyNam e and Phone elem ent s, t he declar at ions lim it t he elem ent values t o st rings. I n addit ion, t he m axim um lengt h is 40 and 24 for t he Com pany Nam e and Phone elem ent s, r espect iv ely. By assigning m inOccurs t o 0, t he schem a per m it s t he Phone elem ent t o be opt ional for each Shippers elem ent . The ShipperI D and Com panyNam e elem ent s are required child elem ent s for each Shipper s elem ent .
t hr ee at t ribut es. Not ice t hat y ou can use t he sam e sim pleType elem ent s for declaring at t ribut es t hat you use for declaring elem ent s in an XML docum ent . I n spit e of using t he sam e sim pleType elem ent s as t he pr eceding schem a, t his schem a differs from t he preceding one by declaring ShipperI D, Com pany Nam e, and Phone as at t r ibut es inst ead of elem ent s.
schem a link ing t o a row source. SQL Serv er has a special t ool for cr eat ing v irt ual dir ect or ies on I I S server s t hat point t o specific SQL Ser ver dat abases. These virt ual dir ect ories perm it annot at ed schem as t o connect t o a SQL Ser ver dat abase and der iv e a r owset . N ot e One of t he innovat ions of Web Release 2 is t hat t he form at t ing of t he ret urned rowset can t ake place on t he I I S server inst ead of SQL Serv er. By t ransfer ring t he for m at t ing of t he ret urned rowset fr om t he dat abase server t o t he I I S server , Micr osoft can event ually pr ov ide v iews based on annot at ed schem as for ot her t han SQL Server dat abases. This sect ion int roduces t he basics of annot at ed schem a design and use. A lat er sect ion, “ Virt ual Direct ory Managem ent ,” drills down on v irt ual direct or ies. An annot at ed schem a is an XML docum ent j ust like a norm al XSD schem a. Howev er, you norm ally wr it e it wit hout t he XML v ersion declarat ion. I n addit ion, you m ust add a new nam espace r efer ence ( schem as- m icr osoft - com : m apping- schem a) t o t he schem a shell t o accom m odat e special annot at ion elem ent s and at t r ibut es. This Microsoft m apping nam espace support s t he special feat ures t hat perm it annot at ion of XSD schem a so t hey link t o one or m ore row sources in a SQL Ser ver dat abase. The sql designat or for t he nam espace is arbit rar y ; y ou can use any ot her legit im at e XML nam e. ... Two at t ribut es for link ing a schem a t o a row source in a dat abase include t he relat ion at t r ibut e and t he field at t r ibut e. Precede t hese at t r ibut e nam es and any ot her t hat you use for annot at ing y our schem a wit h t he designat or for t he Micr osoft m apping nam espace. The relat ion at t ribut e cr eat es a link bet ween a com plex elem ent and a SQL Ser ver row source. Using t he r elat ion at t ribut e let s you cr eat e an alias in y our annot at ed schem a for t he row source nam e in a SQL Ser ver dat abase. The schem a w ill at t em pt t o m at ch t he child elem ent s and at t ribut es for t he com plex elem ent t o t he colum ns fr om t he row source. I f t he at t ribut es and child elem ent s hav e nam es t hat m at ch colum n nam es in t he r ow source, y ou don’t need t o specify a field at t r ibut e for t he at t ribut e or elem ent . I f t he at t r ibut e or child elem ent nam e doesn’t m at ch t he nam e for a colum n in t he row source, y ou can specify t he field at t ribut e. Wit h t he field at t r ibut e, you can explicit ly link an elem ent or at t r ibut e t o a colum n in t he r ow source specified by a relat ion at t r ibut e. N ot e I f a com plex elem ent nam e in a schem a m at ches a row source nam e in a dat abase, you don’t need t o designat e t he corr espondence bet ween t he t wo wit h t he relat ion at t r ibut e. The follow ing annot at ed schem a dem onst rat es t he use of t he r elat ion and field at t ribut es. The schem a for m at s an XML docum ent w it h a com plex elem ent t ype nam ed xm lShippers t hat has t wo child elem ent s, xm lCom panyNam e and xm lPhone, and an at t ribut e, Shipper I D. Not ice t hat t he r elat ion and field at t ribut es appear w it h a sql pr efix t o specify t he nam espace for defining t he at t ribut es. The sql: r elat ion at t ribut e point s t he x m lShippers elem ent t o t he Shippers r ow source. The sql: field at t r ibut es for t he x m lCom panyNam e and Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xm lPhone elem ent s link t hese elem ent s t o t he Com panyNam e and Phone colum ns in t he Shippers r ow source. Because t he ShipperI D at t r ibut e nam e m at ches a colum n in t he Shippers row source, it doesn’t requir e a sql: field at t ribut e set t ing t o link it t o a colum n wit hin t he row source. Wit h t hr ee m or e st eps, you can r et ur n an XML docum ent based on t he Shippers row source. 1. Sav e t he annot at ed schem a in a v irt ual dir ect or y configured t o connect t o t he Nort hw ind dat abase. Because t he Nort hw ind t able cont ains a t able nam ed Shippers, t his defines t he r ow source in t he annot at ed schem a. 2. Creat e a new XML docum ent , called a t em plat e, t hat inv ok es t he annot at ed schem a. By inv ok ing t he quer y wit h XPat h sy nt ax, t he t em plat e file can cause t he annot at ed schem a t o ret urn a view of t he Shippers t able as an XML docum ent . 3. Nav igat e t o t he t em plat e from a browser t o ret ur n t he v iew specified by t he t em plat e t o t he br owser’s docum ent window. The follow ing XML docum ent illust rat es t he synt ax for referring t o t he preceding annot at ed schem a in x m lShippersSchem a.xm l. The specificat ion r equir es t he schem a t o r eside in a special t em plat e folder w it hin t he v irt ual direct or y, but y ou can explicit ly designat e anot her source for t he annot at ed schem a file. The act ual query sy nt ax sim ply refer ences t he elem ent w it h t he sql: r elat ion at t ribut e set t ing, nam ely x m lShippers. This form of an XPat h query request s t he ret urn of all t he rows fr om t he Shippers t able. The XPat h query is equivalent t o SELECT * FROM Shippers in T- SQL. /xmlShippers The t op screen shot in Figur e 6- 2 shows t he Shippers t able in XML form at based on t he annot at ed schem a in xm lShippersSchem a.xm l and t he t em plat e file ( xm lShippersSchem aT.x m l) t hat quer ies t he schem a. The browser ’s Address box shows t he pat h t o t he t em plat e file t hat cont ains t he XPat h query . The t em plat e resides on an I I S serv er nam ed ccs1. The t em plat e is in t he t em plat e folder of t he MyNw ind v irt ual direct ory. The use of t he nam e t em plat e for t he t em plat e folder is arbit rary . Any ot her nam e w ill serv e equally well. The XML docum ent in t he browser w indow follows t he form at of t he annot at ed schem a. Not ice t hat Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.