SQL Server XML Statistics and Execution Plans

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.