CREATE TABLE Storage
(
ID INT IDENTITY(1,1) PRIMARY KEY,
data XML NOT NULL
)
GO
INSERT INTO Storage(data)
VALUES('
<footballteams>
<team manager="Benitez">
<name>Liverpool</name>
<ground>Anfield</ground>
</team>
<team manager="Mourinho">
<name>Chelsea</name>
<ground>Stamford Bridge</ground>
</team>
<team manager="Wenger">
<name>Arsenal</name>
<ground>Highbury</ground>
</team>
</footballteams>');
----------------------------------------
GO
CREATE VIEW FootballView WITH SCHEMABINDING AS
(
SELECT
TeamName = Team.TeamNode.value('(name)[1]', 'varchar(100)'),
Manager = Team.TeamNode.value('(@manager)', 'varchar(100)'),
Ground = Team.TeamNode.value('(ground)[1]', 'varchar(100)')
FROM
dbo.Storage S
CROSS APPLY DATA.nodes('/footballteams') AS Teams(TeamsNode)
CROSS APPLY data.nodes('/footballteams/team') AS Team(TeamNode)
)
GO
CREATE UNIQUE CLUSTERED INDEX TeamNameInd ON FootballView(TeamName)
-
-
Error Message: Cannot create index on view "CF.dbo.FootballView" because it contains an APPLY. Consider not indexing the view, or removing APPLY.
I realise that, indeed, an index can't be created for this view because CROSS APPLY is used. Can anyone suggest a workaround for this? As working with views like this which aren't indexed is too slow when it deals with higher volumes of XML data.
我意识到,实际上,无法为此视图创建索引,因为使用了CROSS APPLY。任何人都可以为此建议解决方法吗?在处理更大量的XML数据时,处理未编制索引的视图太慢。
EDIT:
编辑:
Is there any way I could index the XML itself?
有什么方法可以索引XML本身吗?
1 个解决方案
#1
1
You can solve this problem (and probably achieve better performance) by parsing XML data into a SQL relational table and keeping the tables in-synch by using a simple trigger. The following script is a sample of how it can be done and I hope it is useful to your case:
您可以通过将XML数据解析为SQL关系表并使用简单的触发器使表保持同步来解决此问题(并且可能实现更好的性能)。以下脚本是如何完成的示例,我希望它对您的案例有用:
CREATE TABLE Storage (
id INT IDENTITY(1,1) PRIMARY KEY,
data XML NOT NULL
);
CREATE TABLE FootballTable (
id INT,
teamName varchar(100),
manager varchar(100),
ground varchar(100)
);
GO
CREATE TRIGGER TG_INS_Storage ON Storage
FOR INSERT, UPDATE, DELETE AS
BEGIN
DELETE FootballTable WHERE id IN (SELECT dlt.id FROM deleted dlt);
INSERT FootballTable (id, teamName, manager, ground)
SELECT ins.id,
TeamName = Team.TeamNode.value('(name)[1]', 'varchar(100)'),
Manager = Team.TeamNode.value('(@manager)', 'varchar(100)'),
Ground = Team.TeamNode.value('(ground)[1]', 'varchar(100)')
FROM inserted ins
CROSS APPLY ins.data.nodes('/footballteams/team') AS Team(TeamNode);
END
INSERT INTO Storage(data)
VALUES('
<footballteams>
<team manager="Benitez"><name>Liverpool</name><ground>Anfield</ground></team>
<team manager="Mourinho"><name>Chelsea</name><ground>Stamford Bridge</ground></team>
<team manager="Wenger"><name>Arsenal</name><ground>Highbury</ground></team>
</footballteams>');
GO
SELECT * FROM FootballTable;
#1
1
You can solve this problem (and probably achieve better performance) by parsing XML data into a SQL relational table and keeping the tables in-synch by using a simple trigger. The following script is a sample of how it can be done and I hope it is useful to your case:
您可以通过将XML数据解析为SQL关系表并使用简单的触发器使表保持同步来解决此问题(并且可能实现更好的性能)。以下脚本是如何完成的示例,我希望它对您的案例有用:
CREATE TABLE Storage (
id INT IDENTITY(1,1) PRIMARY KEY,
data XML NOT NULL
);
CREATE TABLE FootballTable (
id INT,
teamName varchar(100),
manager varchar(100),
ground varchar(100)
);
GO
CREATE TRIGGER TG_INS_Storage ON Storage
FOR INSERT, UPDATE, DELETE AS
BEGIN
DELETE FootballTable WHERE id IN (SELECT dlt.id FROM deleted dlt);
INSERT FootballTable (id, teamName, manager, ground)
SELECT ins.id,
TeamName = Team.TeamNode.value('(name)[1]', 'varchar(100)'),
Manager = Team.TeamNode.value('(@manager)', 'varchar(100)'),
Ground = Team.TeamNode.value('(ground)[1]', 'varchar(100)')
FROM inserted ins
CROSS APPLY ins.data.nodes('/footballteams/team') AS Team(TeamNode);
END
INSERT INTO Storage(data)
VALUES('
<footballteams>
<team manager="Benitez"><name>Liverpool</name><ground>Anfield</ground></team>
<team manager="Mourinho"><name>Chelsea</name><ground>Stamford Bridge</ground></team>
<team manager="Wenger"><name>Arsenal</name><ground>Highbury</ground></team>
</footballteams>');
GO
SELECT * FROM FootballTable;