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

69
lượt xem 11
download

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

Mô tả tài liệu

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.