【原创】XML数据库的操作
这篇主要介绍在数据库中如何对XML数据类型的操作,见代码,
XMLData OP
/* [Agent] is a Database name*/
USE [Agent]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Agent_Item]') AND type in (N'U'))
DROP TABLE [dbo].[Agent_Item]
USE [Agent]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Agent_Item](
[ID] [bigint] NOT NULL,
[ItemXMLData] [xml] NULL
) ON [PRIMARY]
INSERT INTO [Agent].[dbo].[Agent_Item]
([ID]
,[ItemXMLData])
VALUES
(1,null)
INSERT INTO [Agent].[dbo].[Agent_Item]
([ID]
,[ItemXMLData])
VALUES
(2,null)
UPDATE [Agent_Item]
SET [ItemXMLData] =
'<?xml version="1.0"?><Root>
<Element Id="1" Att="False" />
<Element Id="2" Att="True" />
</Root>
'
SELECT [ItemXMLData].query(' /Root/Element')
FROM [Agent_Item]
UPDATE [Agent_Item]
SET [ItemXMLData].modify('replace value of (/Root/Element/@Att)[1] with "True"')
SELECT [ItemXMLData].query('/Root/Element')
FROM [Agent_Item]
UPDATE [Agent_Item]
SET [ItemXMLData].modify('replace value of (/Root/Element/@Att)[2] with "True"')
SELECT [ItemXMLData].query('/Root/Element')
FROM [Agent_Item]
DELETE [Agent_Item]
/* [Agent] is a Database name*/
USE [Agent]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Agent_Item]') AND type in (N'U'))
DROP TABLE [dbo].[Agent_Item]
USE [Agent]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Agent_Item](
[ID] [bigint] NOT NULL,
[ItemXMLData] [xml] NULL
) ON [PRIMARY]
INSERT INTO [Agent].[dbo].[Agent_Item]
([ID]
,[ItemXMLData])
VALUES
(1,null)
INSERT INTO [Agent].[dbo].[Agent_Item]
([ID]
,[ItemXMLData])
VALUES
(2,null)
UPDATE [Agent_Item]
SET [ItemXMLData] =
'<?xml version="1.0"?><Root>
<Element Id="1" Att="False" />
<Element Id="2" Att="True" />
</Root>
'
SELECT [ItemXMLData].query(' /Root/Element')
FROM [Agent_Item]
UPDATE [Agent_Item]
SET [ItemXMLData].modify('replace value of (/Root/Element/@Att)[1] with "True"')
SELECT [ItemXMLData].query('/Root/Element')
FROM [Agent_Item]
UPDATE [Agent_Item]
SET [ItemXMLData].modify('replace value of (/Root/Element/@Att)[2] with "True"')
SELECT [ItemXMLData].query('/Root/Element')
FROM [Agent_Item]
DELETE [Agent_Item]