Xml programming bible phần 6

Đó là ngụ ý của các yếu tố làm tổ, ví dụ, AuthorID và sourceid cho các dữ liệu Báo giá nên được cung cấp bởi AuthorID trong các yếu tố cha mẹ tác giả. Sử dụng OpenXML để chèn dữ liệu XML | 456 Part IV Relational Data and XML The nested elements don t contain references to the AuthorlD or SourcelD columns even though these values are part of the SQL Server tables represented by the nested elements. This information is supplied by the foreign key relationships in the tables and represented by the way that the elements are nested. It s implied by the element nesting that for example the AuthorlD and the SourcelD for the Quotations data should be supplied by the AuthorlD in the Authors parent element. Quotations QuotationID 1001 Quotation When the hurlyburly s done When the battle s lost and won. Sources Authors XMLProgrammingBIble Using OPENXML to insert XML data I ve show you what XML document node trees look like when they are created by OPENXML in an edge table and I ve provided an overview of the XML document that I am using for the OPENXML examples. Now I can use OPENXML expressions to insert data into related tables. The code in Listing 18-4 inserts data into three tables from a single XML document. Listing 18-4 Inserting Data into SQL Server Tables Using OPENxMl - DECLARE @iDoc int @cDoc varchar 5000 SET @cDoc XMLProgrammingBible Authors AuthorID 1001 AuthorName Shakespeare William Sources SourceID 1001 Source_Name Macbeth Quotations QuotationID 1001 Quotation When the hurlyburlys done When the battles lost and won. Sources Authors XMLProgrammingBible EXEC sp_xml_preparedocument @iDoc OUTPUT @cDoc INSERT INTO XMLProgrammingBible . dbo . Authors AuthorID AuthorName SELECT AuthorID AuthorName FROM OPENXML @iDoc XMLProgrammingBible Authors WITH Authors INSERT INTO XMLProgrammingBible . dbo . Sources SourceID Source Name SELECT SourceID Source Name FROM OPENXML @iDoc XMLProgrammingBible Authors Sources WITH Sources INSERT INTO XMLProgrammingBible . dbo . Quotations QuotationID SourceID AuthorID Quotation Chapter 18 Accessing and Formatting XML from SQL Server Data 457 SELECT QuotationID SourcelD AuthorID Quotation FROM OPENXML .