DataTamer: SQL Server Services
Contact Us
Customer Site
Home
Services
Blog
Articles
Code
Downloads
About Us
Property Bag
SET NOCOUNT ON GO CREATE TABLE [dbo].[tester] ( [testerID] INT IDENTITY(1,1) NOT NULL CONSTRAINT [tester_primary] PRIMARY KEY CLUSTERED ,[CreateDate] DATETIME NOT NULL CONSTRAINT [tester_CreateDate] DEFAULT (GETUTCDATE()) ,[properties] XML NULL ) GO DECLARE @row_count INT = 1 TRUNCATE TABLE [dbo].[tester] WHILE @row_count <= 1000 BEGIN INSERT [dbo].[tester] ([properties]) SELECT ( SELECT [Name] AS [@Name] ,[Units] AS [@Units] ,[Value] AS [@Value] FROM ( SELECT 'Inventory #' AS [Name] ,'ABC' AS [Value] ,'N/A' AS [Units] UNION ALL SELECT 'Cost' AS [Name] ,'15.00' AS [Value] ,'$' AS [Units] UNION ALL SELECT 'DeleteMe' AS [Name] ,'999' AS [Value] ,'CAD' AS [Units] ) AS A FOR XML PATH('Property') ) SELECT @row_count = @row_count + 1 END GO SELECT TOP 100 * FROM [dbo].[tester] GO -- select multiple records, must pivot SELECT TOP 100 [testerID] ,[CreateDate] ,[properties].value('(/CustomProperties/Property[@Name="Inventory #"]/@Value)[1]','nvarchar(200)') AS [Inventory #] ,[properties].value('(/CustomProperties/Property[@Name="Inventory #"]/@Units)[1]','nvarchar(200)') AS [Inventory # Units] ,[properties].value('(/CustomProperties/Property[@Name="Cost"]/@Value)[1]',' nvarchar(200)') AS [Cost] ,[properties].value('(/CustomProperties/Property[@Name="Cost"]/@Units)[1]',' nvarchar(200)') AS [Cost Units] ,[properties].value('(/CustomProperties/Property[@Name="Blank"]/@Value)[1]', 'nvarchar(200)') AS [Blank] ,[properties].value('(/CustomProperties/Property[@Name="Blank"]/@Units)[1]', 'nvarchar(200)') AS [Blank Units] FROM [dbo].[tester] GO -- search multiple records, must pivot SELECT TOP 100 [testerID] ,[CreateDate] ,[properties].value('(/CustomProperties/Property[@Value="15.00"]/@Name)[1]', 'nvarchar(200)') AS [PropertyName] ,[properties].value('(/CustomProperties/Property[@Value="15.00"]/@Value)[1]' ,'nvarchar(200)') AS [PropertyValue] ,[properties].value('(/CustomProperties/Property[@Value="15.00"]/@Units)[1]' ,'nvarchar(200)') AS [PropertyUnits] FROM [dbo].[tester] WHERE [properties].exist('/CustomProperties/Property[@Value="15.00"]') = 1 GO -- select one record, must shred SELECT [testerID] ,[CreateDate] ,x.y.value('@Name','varchar(500)') AS [PropertyName] ,x.y.value('@Value','varchar(500)') AS [PropertyValue] ,x.y.value('@Units','varchar(500)') AS [PropertyUnits] FROM [dbo].[tester] CROSS APPLY [properties].nodes(N'/CustomProperties/Property') AS x(y) -- select all Property nodes below CustomProperties WHERE [testerID] = 1 GO CREATE PRIMARY XML INDEX [tester_xml] ON [dbo].[tester] ([properties]) GO set statistics io on -- select distinct list of all custom properties, must shred SELECT DISTINCT x.y.value('@Name','varchar(500)') AS [PropertyName] FROM [dbo].[tester] CROSS APPLY [properties].nodes(N'/Property') AS x(y) -- select all Property nodes -- NO INDEX: Table 'tester'. Scan count 9, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. -- WITH INDEX: Table 'tester'. Scan count 1, logical reads 31, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. set statistics io off GO UPDATE [dbo].[tester] SET [properties].modify('delete /Property[@Name="DeleteMe"]') GO SELECT TOP 100 * FROM [dbo].[tester] GO DROP TABLE [dbo].[tester] GO
Articles
Naming Conventions
Property Bag
© All rights reserved. DataTamer Inc. 2018