November 22, 2014 at 11:14 pm
Hi,
I want to insert a new node but not sure what the best way to do this.
DECLARE @XML XML = '<Attribute>
<A1>6529</A1>
<A2>6529</A2>
</Attribute>';
DECLARE @n VARCHAR(100) = 'A3';
DECLARE @v-2 VARCHAR(100) = 'New value';
--expected output
DECLARE @XML XML = '<Attribute>
<A1>6529</A1>
<A2>6529</A2>
<A3>New value</A3>
</Attribute>';
Both the name and value are stored procedure parameters, so cannot be hardcoded. Also, the attributes are ordered (sequence specified in XSD) so the position should be determined automatically where the node should be inserted (for instance, ... (/Attribute)[1] will fail for <A3> as it fails validation). Please help. Thank you.
November 23, 2014 at 3:25 am
Quick'n simple example
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<Attribute>
<A1>6529</A1>
<A2>6529</A2>
</Attribute>';
DECLARE @NNODE VARCHAR(100) = 'A3';
DECLARE @nval VARCHAR(100) = 'New value';
DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';
DECLARE @NXML XML;
SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);
SET @TXML.modify('insert sql:variable("@NXML") as last into (/Attribute)[1]');
SELECT @TXML ;
Results
<Attribute>
<A1>6529</A1>
<A2>6529</A2>
<A3>New value</A3>
</Attribute>
November 23, 2014 at 8:21 am
Edit: deleted the post
November 23, 2014 at 1:01 pm
This is not the first time you are trying to help me out. Thank you very much.
I should have mentioned that my XML is validated against a schema that looks something like this:
CREATE XML SCHEMA COLLECTION MySchema AS N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="Attribute">
<xs:complexType>
<xs:sequence>
<xs:element name="A1" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A2" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A3" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A4" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
';
Now, if we run the query:
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TXML XML(MySchema) = '<Attribute>
<A1>6529</A1>
<A2>6529</A2>
<A4>6529</A4>
</Attribute>';
DECLARE @NNODE VARCHAR(100) = 'A3';
DECLARE @nval VARCHAR(100) = 'New value';
DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';
DECLARE @NXML XML;
SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);
SET @TXML.modify('insert sql:variable("@NXML") as last into (/Attribute)[1]');
SELECT @TXML ;
This will result in an error message which is expected due to the sequence:
XML Validation: Unexpected element(s): A3. Location: /*:Attribute[1]/*:A3[1]
What I'm looking for is query that inserts A3 in between A2 and A4.
Also, nodes are optional as you can see in the schema, so
if we had: '<Attribute><A4>6529</A4></Attribute>'
I'd expect A3 to be inserted before A4
if we had: '<Attribute><A1>6529</A1></Attribute>'
I'd expect A3 to be inserted after A1
Hope this makes sense? Thank you again!!!!
November 23, 2014 at 3:07 pm
The XML modify/insert has 4 positioning directives, as first,as last, after and before, changing from as last to after for the correct positioning, note the slight difference in the syntax.
Often the XML must be interrogated prior to modification.
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @TXML XML = '<Attribute>
<A1>6529</A1>
<A2>6529</A2>
</Attribute>';
DECLARE @NNODE VARCHAR(100) = 'A3';
DECLARE @nval VARCHAR(100) = 'New value';
DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';
DECLARE @NXML XML;
SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);
SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/A2)[1]');
SELECT @TXML ;
November 23, 2014 at 7:22 pm
Thank you.
Is there a way to replace A2 with something so that it is not hard-coded?
SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/A2)[1]');
No guarantees that A2 node will exist as they are optional. In this case A3 would go after A1 (or before A4). Producing:
A1
A3
A4
November 24, 2014 at 3:29 am
As I mentioned before, often the XML must be interrogated to determine the placement of the inserted node. This sample checks both the XML to be modified and the XML Schema Collection to correctly place the node insert.
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
CREATE XML SCHEMA COLLECTION MySchema AS N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="Attribute">
<xs:complexType>
<xs:sequence>
<xs:element name="A1" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A2" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A3" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A4" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
';
GO
DECLARE @TXML XML = '<Attribute>
<A1>6700</A1>
<A4>6730</A4>
</Attribute>';
DECLARE @XMLSCHEMA_NAME NVARCHAR(128) = N'MySchema'
DECLARE @NNODE VARCHAR(100) = 'A3';
DECLARE @nval VARCHAR(100) = 'New value';
DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';
DECLARE @NXML XML;
DECLARE @XSTNODE VARCHAR(100) = '';
;WITH XML_SCHEMA_STRUCTURE AS
(
SELECT
XSC.name
,XCP.placement_id
FROM sys.xml_schema_collections XC
INNER JOIN sys.xml_schema_components XSC
ON XC.xml_collection_id = XSC.xml_collection_id
INNER JOIN sys.xml_schema_component_placements XCP
ON XSC.xml_component_id = XCP.placed_xml_component_id
WHERE XC.name = @XMLSCHEMA_NAME
AND XSC.name IS NOT NULL
)
,XML_STRUCTURE AS
(
SELECT
TNODE.DATA.value('local-name(.)','VARCHAR(100)') AS XNODE
FROM @TXML.nodes('/Attribute/*') AS TNODE(DATA)
)
,NODE_TO_INSERT AS
(
SELECT
XSS.name
,XSS.placement_id
FROM XML_SCHEMA_STRUCTURE XSS
WHERE XSS.name = @NNODE
)
SELECT
@XSTNODE = (SELECT TOP(1) XSS.name
FROM XML_SCHEMA_STRUCTURE XSS
LEFT OUTER JOIN XML_STRUCTURE XS
ON XSS.name = XS.XNODE
WHERE XSS.placement_id < (SELECT TOP(1) placement_id
FROM NODE_TO_INSERT NTI)
AND XS.XNODE IS NOT NULL
ORDER BY XSS.placement_id DESC);
SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);
IF (@XSTNODE IS NOT NULL)
BEGIN
SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/*[local-name() = sql:variable("@XSTNODE")])[1]');
END
IF (@XSTNODE IS NULL)
BEGIN
SET @TXML.modify('insert sql:variable("@NXML") as first into (/Attribute)[1]');
END
SELECT @TXML;
GO
DROP XML SCHEMA COLLECTION MySchema;
GO
Results
<Attribute>
<A1>6700</A1>
<A3>New value</A3>
<A4>6730</A4>
</Attribute>
November 24, 2014 at 8:21 am
Here is a slightly different take on the suggestion by Eirikur that achieves the same but without querying the system views. I've also opted to use dynamic sql to generate the additional xml section rather than a search/replace. This ensures that non-xml safe characters in the node value are escaped properly but you need to assess the risk of using dynamic SQL and if you trust what is going to be invoking the logic.
DECLARE @XML XML = '<Attribute>
<A1>1111</A1>
<A2>2222</A2>
<A4>4444</A4>
</Attribute>';
DECLARE @n VARCHAR(100) = 'A3';
DECLARE @v-2 VARCHAR(100) = 'New value';
DECLARE @XmlToInsert XML
DECLARE @sql NVARCHAR(200)
SET @sql = N'SELECT @XmlToInsert = (SELECT @v-2 FOR XML PATH(''' + @n + '''))'
EXEC sys.sp_executesql @sql, N'@XmlToInsert XML OUTPUT, @v-2 VARCHAR(100)'
, @XmlToInsert = @XmlToInsert OUTPUT
IF @XML.value('count(/Attribute/node())', 'int') > 0
SET @XML.modify('insert sql:variable("@XmlToInsert") before (/Attribute/node()[local-name(.) > sql:variable("@n") ])[1]')
ELSE
SET @XML.modify('insert sql:variable("@XmlToInsert") as first into (/Attribute)[1]')
SELECT @XML
November 24, 2014 at 12:38 pm
Good catch with the node value Arthur, don't think it merits a dynamic sql though as one can easily encode the value with a simple FOR XML statement.
😎
/* Simple encoding statement using FOR XML
*/
SELECT @nval = (
SELECT
'' + @nval
FOR XML PATH('')
);
The reason for querying the xml schema collection views is simply the fact that one cannot rely on the alphabetical order of the node names in a sequence, hence the schema definition is the only reliable source. The query sample is very simple though, real life applications are normally quite complicated and often it is simpler to have either a table or a hard coded representation.
This code sample has a sequence of non alphabetical order and a node value containing "illegal" characters.
USE tempdb;
GO
SET NOCOUNT ON;
GO
CREATE XML SCHEMA COLLECTION MySchema AS N'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="Attribute">
<xs:complexType>
<xs:sequence>
<xs:element name="A4" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A1" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A2" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
<xs:element name="A3" type="xs:string" minOccurs="0" maxOccurs="1"></xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
';
GO
DECLARE @TXML XML = '<Attribute>
<A4>6700</A4>
<A1>6730</A1>
</Attribute>';
DECLARE @XMLSCHEMA_NAME NVARCHAR(128) = N'MySchema'
DECLARE @NNODE VARCHAR(100) = 'A3';
DECLARE @nval VARCHAR(100) = 'New </>?;:value';
DECLARE @Nt VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';
DECLARE @NXML XML;
DECLARE @XSTNODE VARCHAR(100) = '';
;WITH XML_SCHEMA_STRUCTURE AS
(
SELECT
XSC.name
,XCP.placement_id
FROM sys.xml_schema_collections XC
INNER JOIN sys.xml_schema_components XSC
ON XC.xml_collection_id = XSC.xml_collection_id
INNER JOIN sys.xml_schema_component_placements XCP
ON XSC.xml_component_id = XCP.placed_xml_component_id
WHERE XC.name = @XMLSCHEMA_NAME
AND XSC.name IS NOT NULL
)
,XML_STRUCTURE AS
(
SELECT
TNODE.DATA.value('local-name(.)','VARCHAR(100)') AS XNODE
FROM @TXML.nodes('/Attribute/*') AS TNODE(DATA)
)
,NODE_TO_INSERT AS
(
SELECT
XSS.name
,XSS.placement_id
FROM XML_SCHEMA_STRUCTURE XSS
WHERE XSS.name = @NNODE
)
SELECT
@XSTNODE = (SELECT TOP(1) XSS.name
FROM XML_SCHEMA_STRUCTURE XSS
LEFT OUTER JOIN XML_STRUCTURE XS
ON XSS.name = XS.XNODE
WHERE XSS.placement_id < (SELECT TOP(1) placement_id
FROM NODE_TO_INSERT NTI)
AND XS.XNODE IS NOT NULL
ORDER BY XSS.placement_id DESC);
/* Simple encoding statement using a
CTE and FOR XML
*/
;WITH NVALUE_STR AS
(
SELECT @nval AS XSTR
)
SELECT @nval = (
SELECT
'' + NV.XSTR
FROM NVALUE_STR NV
FOR XML PATH('')
);
SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);
IF (@XSTNODE IS NOT NULL)
BEGIN
SET @TXML.modify('insert sql:variable("@NXML") after (/Attribute/*[local-name() = sql:variable("@XSTNODE")])[1]');
END
IF (@XSTNODE IS NULL)
BEGIN
SET @TXML.modify('insert sql:variable("@NXML") as first into (/Attribute)[1]');
END
SELECT @TXML;
GO
DROP XML SCHEMA COLLECTION MySchema;
GO
Results
<Attribute>
<A4>6700</A4>
<A1>6730</A1>
<A3>New </>?;:value</A3>
</Attribute>
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply