QL Scripts
–Test Table
IF EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'[TableA]’) )
DROP TABLE TableA
GO
CREATE TABLE TableA (
GroupID int NOT NULL,
ID int NOT NULL,
rMoney money NOT NULL,
rDate datetime NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ( [ID] ) WITH FILLFACTOR = 100 )
GO
SET NOCOUNT ON
DECLARE @BatchTotal int, @BatchSize int, @TotalRows int, @BatchStart int, @BatchEnd int, @BatchRow int, @I int, @RowsPerPage int, @Card int, @DistinctValues int
SELECT @BatchStart=1, @BatchEnd=1, @BatchTotal=1, @BatchSize=100200, @RowsPerPage=100, @Card=300
SELECT @TotalRows=@BatchTotal*@BatchSize SELECT @I=(@BatchStart-1)*@BatchSize+1, @DistinctValues=@TotalRows/@Card
WHILE @BatchStart <= @BatchEnd BEGIN BEGIN TRANSACTION
SELECT @BatchRow = @BatchStart*@BatchSize WHILE @I <= @BatchRow BEGIN
INSERT TableA (GroupID, ID, rMoney, rDate)
VALUES ( (@I-1)/@Card+1, @I, 10000*rand(), DATEADD(hour,@I,’1900-01-01′) )
IF @@ERROR > 0 BEGIN GOTO B END SET @I = @I+1 END COMMIT TRANSACTION CHECKPOINT
SET @BatchStart = @BatchStart+1
END B: IF @@TRANCOUNT > 0 COMMIT TRANSACTION
GO
CREATE INDEX IX_GroupID ON TableA(GroupID)
GO
Demo 1.
DECLARE @XML nvarchar(4000) DECLARE @idoc INT
SELECT @XML = N'<ROOT><Group1>
<Test GroupID=”1″ ID=”1″ rMoney=”10.10″ rDate=”2005-01-01″/>
<Test GroupID=”1″ ID=”2″ rMoney=”10.20″ rDate=”2005-01-02″/>
<Test GroupID=”1″ ID=”3″ rMoney=”11.20″ rDate=”2005-02-02″/>
</Group1></ROOT>’
exec sp_xml_preparedocument @idoc OUTPUT, @XML
SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime)
SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime)
WHERE GroupID = 1
Demo 2
DECLARE @XML nvarchar(4000) DECLARE @idoc INT
SELECT @XML = N'<ROOT><Group1>
<Test GroupID=”1″ ID=”1″ rMoney=”10.10″ rDate=”2005-01-01″/>
<Test GroupID=”1″ ID=”2″ rMoney=”10.20″ rDate=”2005-01-02″/>
<Test GroupID=”1″ ID=”3″ rMoney=”11.20″ rDate=”2005-02-02″/>
</Group1></ROOT>’
exec sp_xml_preparedocument @idoc OUTPUT, @XML
SELECT * FROM ( SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime) ) s
INNER JOIN ( SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime) ) t ON t.ID = s.ID
Demo 3
DECLARE @XML nvarchar(4000) DECLARE @idoc INT
SELECT @XML = N'<ROOT><Group1>
<Test GroupID=”1″ ID=”1″ rMoney=”10.10″ rDate=”2005-01-01″/>
<Test GroupID=”1″ ID=”2″ rMoney=”10.20″ rDate=”2005-01-02″/>
<Test GroupID=”1″ ID=”3″ rMoney=”11.20″ rDate=”2005-02-02″/>
</Group1></ROOT>’
exec sp_xml_preparedocument @idoc OUTPUT, @XML
SELECT * FROM TableA t INNER JOIN ( SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime) ) x
ON t.ID = x.ID
WHERE t.GroupID = 1
SELECT * FROM TableA t INNER JOIN ( SELECT TOP 100 * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime) ) x
ON t.ID = x.ID
WHERE t.GroupID = 1
Demo 4
DECLARE @XML nvarchar(4000) DECLARE @idoc INT
SELECT @XML = N'<ROOT><Group1>
<Test GroupID=”1″ ID=”1″ rMoney=”10.10″ rDate=”2005-01-01″/>
<Test GroupID=”1″ ID=”2″ rMoney=”10.20″ rDate=”2005-01-02″/>
<Test GroupID=”1″ ID=”3″ rMoney=”11.20″ rDate=”2005-02-02″/>
</Group1></ROOT>’
exec sp_xml_preparedocument @idoc OUTPUT, @XML
INSERT TableA SELECT * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime)
INSERT TableA SELECT TOP 500 * FROM OPENXML (@idoc, ‘/ROOT/Group1/Test’,1)
WITH (GroupID int, ID int, rMoney money, rDate datetime)
Copyright 2005 by the Author.
]]>