- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 1
CREATE TABLE [dbo].[XmlModificationLanguageExample]( [ID] [uniqueidentifier] NOT NULL, [XmlField] [xml] NULL, CONSTRAINT [PK_XmlModificationLanguageExample] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]Final goal is to have XML like:
<root> <Parent Name="IamFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value=" <root> <Parent Name="IamTheSonOfMyFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="MyKids" Type="Geni.Files" Value=" <root> <Children Kind="Male" Count="12" ChildName="Stanko"> <Value Name="Name" Type="Male.Child" Value="48yrsOld"/> </Children> </root> " /> </Parent> </root> " /> </Parent> </root>First, lets insert XML like:
<root> <Parent Name="IamFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value="" /> </Parent> </root>
INSERT INTO [dbo].[XmlModificationLanguageExample] ([ID] ,[XmlField]) VALUES (NEWID() , '<root> <Parent Name="IamFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value="" /> </Parent> </root>')Now first I am gonna prepare XML which I will insert into the node:
<Value Name="Kids" Type="Children" Value="" />
DECLARE @FatherXML XML = '<root> <Parent Name="IamTheSonOfMyFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="MyKids" Type="Geni.Files" Value="" /> </Parent> </root>'; DECLARE @Kids nvarchar(max) = '<root> <Children Kind="Male" Count="12" ChildName="Stanko"> <Value Name="Name" Type="Male.Child" Value="48yrsOld"/> </Children> </root>'; SET @FatherXML.modify(' replace value of ( /root/Parent[@Name="IamTheSonOfMyFather"] /Value[@Name="MyKids"]/@Value)[1] with (sql:variable("@Kids") cast as xs:string?) '); select @FatherXML;Here notice that @Kids is of type nvarchar(max), where @FatherXML is of type XML Now that XML I will insert into main XML, and update the table in server:
DECLARE @FatherAsString NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @FatherXML); UPDATE XmlModificationLanguageExample SET XmlField.modify(' replace value of ( /root/Parent[@Name="IamFather"] /Value[@Name="Kids"]/@Value)[1] with (sql:variable("@FatherAsString") cast as xs:string?) ') WHERE ID = '4D14CD89-002F-4985-B54E-CA2CC0DA6913'; select * from XmlModificationLanguageExampleHere notice that I am converting @FatherXML to NVARCHAR(MAX), and like this I updating attribute. Now, lets say that between nodes:
<Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value="" />I want to add new node:
<Value Name="Youngest" Type="Father" Value="True"/>after "Oldest":
UPDATE XmlModificationLanguageExample SET XmlField.modify(' insert sql:variable("@YoungestXML") after ( /root/Parent[@Name="IamFather"] /Value[@Name="Oldest"] )[1] ') WHERE ID = '4D14CD89-002F-4985-B54E-CA2CC0DA6913'; select * from XmlModificationLanguageExampleHere is the whole SQL query:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XmlModificationLanguageExample]') AND type in (N'U')) DROP TABLE [dbo].[XmlModificationLanguageExample] GO CREATE TABLE [dbo].[XmlModificationLanguageExample]( [ID] [uniqueidentifier] NOT NULL, [XmlField] [xml] NULL, CONSTRAINT [PK_XmlModificationLanguageExample] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO DECLARE @ID uniqueidentifier = NEWID(); INSERT INTO [dbo].[XmlModificationLanguageExample] ([ID] ,[XmlField]) VALUES (@ID , '<root> <Parent Name="IamFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value="" /> </Parent> </root>') DECLARE @FatherXML XML = '<root> <Parent Name="IamTheSonOfMyFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="MyKids" Type="Geni.Files" Value="" /> </Parent> </root>'; DECLARE @Kids nvarchar(max) = '<root> <Children Kind="Male" Count="12" ChildName="Stanko"> <Value Name="Name" Type="Male.Child" Value="48yrsOld"/> </Children> </root>'; SET @FatherXML.modify(' replace value of ( /root/Parent[@Name="IamTheSonOfMyFather"] /Value[@Name="MyKids"]/@Value)[1] with (sql:variable("@Kids") cast as xs:string?) '); SELECT @FatherXML AS FatherXmlData; DECLARE @FatherAsString NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @FatherXML); UPDATE XmlModificationLanguageExample SET XmlField.modify(' replace value of ( /root/Parent[@Name="IamFather"] /Value[@Name="Kids"]/@Value)[1] with (sql:variable("@FatherAsString") cast as xs:string?) ') WHERE ID = @ID; select * from XmlModificationLanguageExample DECLARE @YoungestXML XML = '<Value Name="Youngest" Type="Father" Value="True"/>' UPDATE XmlModificationLanguageExample SET XmlField.modify(' insert sql:variable("@YoungestXML") after ( /root/Parent[@Name="IamFather"] /Value[@Name="Oldest"] )[1] ') WHERE ID = @ID; select * from XmlModificationLanguageExample
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 1
CREATE TABLE [dbo].[XmlModificationLanguageExample]( [ID] [uniqueidentifier] NOT NULL, [XmlField] [xml] NULL, CONSTRAINT [PK_XmlModificationLanguageExample] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]Final goal is to have XML like:
<root> <Parent Name="IamFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value=" <root> <Parent Name="IamTheSonOfMyFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="MyKids" Type="Geni.Files" Value=" <root> <Children Kind="Male" Count="12" ChildName="Stanko"> <Value Name="Name" Type="Male.Child" Value="48yrsOld"/> </Children> </root> " /> </Parent> </root> " /> </Parent> </root>First, lets insert XML like:
<root> <Parent Name="IamFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value="" /> </Parent> </root>
INSERT INTO [dbo].[XmlModificationLanguageExample] ([ID] ,[XmlField]) VALUES (NEWID() , '<root> <Parent Name="IamFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value="" /> </Parent> </root>')Now first I am gonna prepare XML which I will insert into the node:
<Value Name="Kids" Type="Children" Value="" />
DECLARE @FatherXML XML = '<root> <Parent Name="IamTheSonOfMyFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="MyKids" Type="Geni.Files" Value="" /> </Parent> </root>'; DECLARE @Kids nvarchar(max) = '<root> <Children Kind="Male" Count="12" ChildName="Stanko"> <Value Name="Name" Type="Male.Child" Value="48yrsOld"/> </Children> </root>'; SET @FatherXML.modify(' replace value of ( /root/Parent[@Name="IamTheSonOfMyFather"] /Value[@Name="MyKids"]/@Value)[1] with (sql:variable("@Kids") cast as xs:string?) '); select @FatherXML;Here notice that @Kids is of type nvarchar(max), where @FatherXML is of type XML Now that XML I will insert into main XML, and update the table in server:
DECLARE @FatherAsString NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @FatherXML); UPDATE XmlModificationLanguageExample SET XmlField.modify(' replace value of ( /root/Parent[@Name="IamFather"] /Value[@Name="Kids"]/@Value)[1] with (sql:variable("@FatherAsString") cast as xs:string?) ') WHERE ID = '4D14CD89-002F-4985-B54E-CA2CC0DA6913'; select * from XmlModificationLanguageExampleHere notice that I am converting @FatherXML to NVARCHAR(MAX), and like this I updating attribute. Now, lets say that between nodes:
<Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value="" />I want to add new node:
<Value Name="Youngest" Type="Father" Value="True"/>after "Oldest":
UPDATE XmlModificationLanguageExample SET XmlField.modify(' insert sql:variable("@YoungestXML") after ( /root/Parent[@Name="IamFather"] /Value[@Name="Oldest"] )[1] ') WHERE ID = '4D14CD89-002F-4985-B54E-CA2CC0DA6913'; select * from XmlModificationLanguageExampleHere is the whole SQL query:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XmlModificationLanguageExample]') AND type in (N'U')) DROP TABLE [dbo].[XmlModificationLanguageExample] GO CREATE TABLE [dbo].[XmlModificationLanguageExample]( [ID] [uniqueidentifier] NOT NULL, [XmlField] [xml] NULL, CONSTRAINT [PK_XmlModificationLanguageExample] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO DECLARE @ID uniqueidentifier = NEWID(); INSERT INTO [dbo].[XmlModificationLanguageExample] ([ID] ,[XmlField]) VALUES (@ID , '<root> <Parent Name="IamFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="Kids" Type="Children" Value="" /> </Parent> </root>') DECLARE @FatherXML XML = '<root> <Parent Name="IamTheSonOfMyFather" Kind="Father"> <Value Name="Oldest" Type="Father" Value="True"/> <Value Name="MyKids" Type="Geni.Files" Value="" /> </Parent> </root>'; DECLARE @Kids nvarchar(max) = '<root> <Children Kind="Male" Count="12" ChildName="Stanko"> <Value Name="Name" Type="Male.Child" Value="48yrsOld"/> </Children> </root>'; SET @FatherXML.modify(' replace value of ( /root/Parent[@Name="IamTheSonOfMyFather"] /Value[@Name="MyKids"]/@Value)[1] with (sql:variable("@Kids") cast as xs:string?) '); SELECT @FatherXML AS FatherXmlData; DECLARE @FatherAsString NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @FatherXML); UPDATE XmlModificationLanguageExample SET XmlField.modify(' replace value of ( /root/Parent[@Name="IamFather"] /Value[@Name="Kids"]/@Value)[1] with (sql:variable("@FatherAsString") cast as xs:string?) ') WHERE ID = @ID; select * from XmlModificationLanguageExample DECLARE @YoungestXML XML = '<Value Name="Youngest" Type="Father" Value="True"/>' UPDATE XmlModificationLanguageExample SET XmlField.modify(' insert sql:variable("@YoungestXML") after ( /root/Parent[@Name="IamFather"] /Value[@Name="Oldest"] )[1] ') WHERE ID = @ID; select * from XmlModificationLanguageExample
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 1416
<NamesTree> <TypeOfName Name="Test" Kind="TestNames"> <Value Name="FirstName" Type="nvarchar(50)" Value="John" /> <Value Name="LastName" Type="nvarchar(50)" Value="Doe" /> <Value Name="Type" Type="int" Value="1" /> </TypeOfName> <TypeOfName Name="Test" Kind="TestNames"> <Value Name="FirstName" Type="nvarchar(50)" Value="Jane" /> <Value Name="LastName" Type="nvarchar(50)" Value="Doe" /> <Value Name="Type" Type="int" Value="2" /> </TypeOfName> </NamesTree>This is how my table look like:
CREATE TABLE [dbo].[Test]( [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [Type] [int] NULL ) ON [PRIMARY]First I am gonna seed my table:
insert into test values ('John', 'Doe', 1) insert into test values ('Jane', 'Doe', 2)This is how SQL looks like:
select 'Test' '@Name' , 'TestNames' '@Kind' , 'FirstName' [Value/@Name] , 'nvarchar(50)' 'Value/@Type' , FirstName 'Value/@Value' , null , 'LastName' [Value/@Name] , 'nvarchar(50)' 'Value/@Type' , LastName 'Value/@Value' , null , 'Type' [Value/@Name] , 'int' 'Value/@Type' , Type 'Value/@Value' , null from test for XML path('TypeOfName'), root('NamesTree')
- Details
- Written by: Stanko Milosev
- Category: MS SQL
- Hits: 1423
declare @myXml xml = ' <params> <param> <value> <array> <data> <value> <string>NameOfValueOne</string> </value> <value> <string>ValueOne</string> </value> </data> </array> </value> </param> <param> <value> <array> <data> <value> <string>NameOfValueTwo</string> </value> <value> <string>ValueTwo</string> </value> </data> </array> </value> </param> </params> ' select T.N.value('.', 'varchar(max)') Value from @myXml.nodes('/params/param/value/array/data/value') T(N)One more example:
declare @myXml xml = ' <param> <value Name="TestNameValue1" Type="TestType1" Value="TestValue1"></value> <value Name="TestNameValue2" Type="TestType1" Value="TestValue2"></value> </param> <param> <value Name="TestNameValue1" Type="TestType2" Value="TestValue3"></value> <value Name="TestNameValue2" Type="TestType2" Value="TestValue4"></value> </param> ' select T.N.value('(value[@Name="TestNameValue1"]/@Value)[1]', 'varchar(max)') TestNameValue1 , T.N.value('(value[@Name="TestNameValue2"]/@Value)[1]', 'varchar(max)') TestNameValue2 from @myXml.nodes('/param') T(N)Another example with OpenXML:
declare @myXml xml = ' <params> <param> <value Name="TestNameValue1" Type="TestType1" Value="TestValue1"></value> <value Name="TestNameValue2" Type="TestType1" Value="TestValue2"></value> </param> <param> <value Name="TestNameValue1" Type="TestType2" Value="TestValue3"></value> <value Name="TestNameValue2" Type="TestType2" Value="TestValue4"></value> </param> </params> ' declare @myXmlDoc int exec sp_xml_preparedocument @myXmlDoc out, @myXml, '<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>' select TestNameValue1, TestNameValue2 from openxml(@myXmlDoc, '/params/param', 2) with ( TestNameValue1 varchar(max) '(value[@Name="TestNameValue1"]/@Value)[1]' , TestNameValue2 varchar(max) '(value[@Name="TestNameValue2"]/@Value)[1]' ) exec sp_xml_removedocument @myXmlDocHere have I found:
As far as performance differences between OPENXML and the XML column type with the nodes() function, there doesn't seem to be a crystal clear answer. If you scan through the SQL Server XML posts on the internet you will find some threads where OPENXML is faster than the XML column's nodes() function and vice-versa. As always the best approach may vary depending on your individual circumstances so don't blindly choose the XML column type and the nodes() function over OPENXML.