Good morning!

I'm trying to learn xml in Sql Server and I made some stepts but here I can't find a solution. Please, may any body help me telling me how to insert a single row into a @xml variable with XMLNAMESPACES in SQL Server 2008R2?

I have the next XML example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test]
(
	[object] [INTEGER] NOT NULL,
	[key] [SMALLINT] NOT NULL,
	[value] [NVARCHAR](MAX) NOT NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
	[object] ASC,
	[key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO [dbo].[test]
([object], [key], [value])
VALUES
(2, 2, N'A'),
(2, 3, N'B'),
(2, 4, N'C')
GO

CREATE XML SCHEMA COLLECTION TestSchema AS
N'<?xml version="1.0" encoding="utf-16"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" targetNamespace="TestSchema" elementFormDefault="qualified">
  <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
  <xsd:element name="element">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="object" type="sqltypes:int" />
        <xsd:element name="key" type="sqltypes:smallint" />
        <xsd:element name="value">
          <xsd:simpleType>
            <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="3082" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" />
          </xsd:simpleType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>';
GO

SELECT
 *
FROM
 [SYS].[XML_SCHEMA_COLLECTIONS]
GO

SELECT
 *
FROM
 [SYS].[XML_SCHEMA_NAMESPACES]
GO

DECLARE @xml AS [XML] (TestSchema);
WITH XMLNAMESPACES (N'TestSchema' AS [TestSchema])
SELECT @xml =
(
 SELECT
   [object] AS [TestSchema:object],
   [key] AS [TestSchema:key],
   [value] AS [TestSchema:value]
  FROM
   [dbo].[test]
 FOR XML RAW (N'TestSchema:element'), ELEMENTS, TYPE
);
SELECT
 @xml;

--DROP TABLE [dbo].[test]
--DROP XML SCHEMA COLLECTION TestSchema

I may insert some rows into the XML wiht a Select Sentence as you can see in the previews example and the result is:

<TestSchema:element xmlns:TestSchema="TestSchema">
  <TestSchema:object>2</TestSchema:object>
  <TestSchema:key>2</TestSchema:key>
  <TestSchema:value>A</TestSchema:value>
</TestSchema:element>
<TestSchema:element xmlns:TestSchema="TestSchema">
  <TestSchema:object>2</TestSchema:object>
  <TestSchema:key>3</TestSchema:key>
  <TestSchema:value>B</TestSchema:value>
</TestSchema:element>
<TestSchema:element xmlns:TestSchema="TestSchema">
  <TestSchema:object>2</TestSchema:object>
  <TestSchema:key>4</TestSchema:key>
  <TestSchema:value>C</TestSchema:value>
</TestSchema:element>

My first question is how can I insert a single row into the xml variable (@xml) something like "SET @myDoc.modify(' insert <Maintenance>3 year parts and labor extended maintenance is available</Maintenance> into (/Root/ProductDescription/Features)[1]') ;" seen in xml but using XMLNAMESPACES?

And my second question is how to set a primary key for the XML (object, key) in my example?

Thanks a lot for your valuable help.

Regards,

Jamesit0.

Hi Jamesit0,

Regarding question one, when mentioning "using XMLNAMESPACES", if you mean adding an element with namespace prefix, then you can reference below sample.

DECLARE @xml AS [XML] --(TestSchema);
WITH XMLNAMESPACES (N'TestSchema' AS [TestSchema])
SELECT @xml =
(
 SELECT
   [object] AS [TestSchema:object],
   [key] AS [TestSchema:key],
   [value] AS [TestSchema:value]
  FROM
   [dbo].[test]
 FOR XML RAW (N'TestSchema:element'), ELEMENTS, TYPE
);


SET @xml.modify('
declare namespace TestSchema="TestSchema";
insert <TestSchema:insertedElement>"this insertedElement is inserted"</TestSchema:insertedElement>
into (/TestSchema:element)[1]')
SELECT @xml

About the second question, what do you mean to set a primary key for XML(object,key)? Would you like to tag each element with unique identifiers such as some attributes as below?

 
DECLARE @xml AS [XML] --(TestSchema);
WITH XMLNAMESPACES (N'TestSchema' AS [TestSchema])
 SELECT @xml=(SELECT
   [object] AS [@object_id],
   [key] AS [@key],
   [object] AS [TestSchema:object],
   [key] AS [TestSchema:key],
   [value] AS [TestSchema:value]
  FROM
   [dbo].[test]
 FOR XML PATH (N'TestSchema:element'), ELEMENTS, TYPE)

 SELECT @xml

If you have any question, feel free to let me know.

Good morning!

Thanks a lot Eric for your reply.

This insert worked for me:

SET @xml.modify(N'declare namespace TestSchema="TestSchema";  insert
<TestSchema:element>
 <TestSchema:object>2</TestSchema:object>
 <TestSchema:key>5</TestSchema:key>
 <TestSchema:value>D</TestSchema:value>
</TestSchema:element> as last
into (/)[1]');

About my second question as I'm new with xml I thing about it like a table. In my table (I gave the code at the begining of this post) I have a composed primary key (object, key). And I don't know if I can represent the same in the xml to avoid insert duplicated items for examplo if some one try to insert:

SET @xml.modify(N'declare namespace TestSchema="TestSchema";  insert
<TestSchema:element>
 <TestSchema:object>1</TestSchema:object>
 <TestSchema:key>1</TestSchema:key>
 <TestSchema:value>A</TestSchema:value>
</TestSchema:element> as last
into (/)[1]');

SET @xml.modify(N'declare namespace TestSchema="TestSchema";  insert
<TestSchema:element>
 <TestSchema:object>1</TestSchema:object>
 <TestSchema:key>1</TestSchema:key>
 <TestSchema:value>B</TestSchema:value>
</TestSchema:element> as last
into (/)[1]');

The xml schema must avoid the second insertion because it is a duplicated Pk item.

Thank a lot for your valuable help.

Regards,

Jamesit0.

Hi Jamesit0,

Based on my research, so far SQL Server doesn't support the  <xsd:unique> which can check duplicated object and key in your case.

Requirements and Limitations for XML Schema Collections on the Server

• Currently, SQL Server does not support these XSD-based constraints for
  enforcing uniqueness or establishing keys and key references. XML
  schemas that contain these elements cannot be registered.

So you can not define the uniqueness in the XML schema collection. As a workaround, you may have check the duplication by yourself.

DECLARE @xml XML='
<TestSchema:element xmlns:TestSchema="TestSchema">
  <TestSchema:object>2</TestSchema:object>
  <TestSchema:key>2</TestSchema:key>
  <TestSchema:value>A</TestSchema:value>
</TestSchema:element>
<TestSchema:element xmlns:TestSchema="TestSchema">
  <TestSchema:object>2</TestSchema:object>
  <TestSchema:key>3</TestSchema:key>
  <TestSchema:value>B</TestSchema:value>
</TestSchema:element>'

SELECT @xml

--@xmlToInsert
DECLARE @xmlToInsert XML='
<TestSchema:element xmlns:TestSchema="TestSchema">
  <TestSchema:object>2</TestSchema:object>
  <TestSchema:key>2</TestSchema:key>
  <TestSchema:value>C</TestSchema:value>
</TestSchema:element>'

--extract the text of object,key and value
;WITH XMLNAMESPACES('TestSchema' AS TestSchema)  
SELECT
x.value('local-name(.)','NVARCHAR(MAX)') elementName,
x.value('.','VARCHAR(30)') AS value
FROM @xml.nodes('/TestSchema:element/*') a(x) 

 

If you have any question, feel free to let me know.

Good morning!

Thanks again for your reply.

Regards,

Jamesit0

Good day.
You're most welcome.