SQL语句与XML.docxVIP

----节示例--CREATE TABLE TABLE2(Col1 int primary key, Col2 xml)----节示例----CREATE DATABASE DBxml;--GO--打开数据库USE DBxml--创建数据表CREATE TABLE DList(Didint, Ddatedatetime, UserIdint, ItemListxml)--1.将XML片段存入变量DECLARE @itemString nvarchar(2000)SET @itemString = Items Item detailId=2 info=3/ Item detailId=4 info=1/ /Items--2.将字符变量转换成为xml类型DECLARE @itemDoc xmlSET @itemDoc = @itemString--3.插入数据INSERT INTO DList VALUES(1, GetDate(), 2, @itemDoc)INSERT INTO DListVALUES(1, GetDate(), 2, Items Item detailId=2 info=3/ Item detailId=4 info=1/ /Items)--4.查看SELECT * FROM DList----(3)节示例--INSERT INTO DListVALUES(1, GetDate(), 2, ?xml version=1.0 ? Items Item detailId=5 info=7/ Item detailId=4 info=1/ /Items)SELECT * FROM DList----(4)节示例---- Not well-formed. This will failINSERT INTO DListVALUES(1, GetDate(), 2, Items Item detailId=2 info=3/----24.3.2(2)节示例---- Create a table that includes XML dataUSE DBxmlGO--1.创建数据表CREATE TABLE TABLE3(Sid integer IDENTITY PRIMARY KEY, SInfo nvarchar(40), SUser nvarchar(40), Info xml)GO----24.3.2(3)节示例----2.插入XML数据INSERT INTO TABLE3 VALUES(ABC Company, Jeff Adell, DListList xmlns=/InfoDList DListNo=1000 CustomerKim Abercrombie/Customer Items Item Product=1 Price=1.99 Quantity=2/ Item Product=3 Price=2.49 Quantity=1/ /Items /DList DList DListNo=1001 CustomerSean Chai/Customer Items Item Product=1 Price=2.45 Quantity=2/ /Items /DList/DListList)INSERT INTO TABLE3 VALUES(WEBSite, Karen Berge, DListList xmlns=/InfoDList DListNo=999 CustomerSarah Akhtar/Customer Items Item Product=8 Price=2.99 Quantity=3/ /Items/DListDList DListNo=1000 CustomerBei-Jing Guo/Customer Items Item Product=1 Price=1.95 Quantity=7/ Item Product=100 Price=112.99 Quantity=1/ /Items/DList/DListList)INSERT INTO TABLE3 VALUES (eBAI, Scott Cooper, N