Can I use XML.modify in combination with SELECT statement

Question

Post reply

Can I use XML.modify in combination with SELECT statement

Harry Drenth

Say Hey Kid

Points: 678

July 20, 2022 at 8:37 am

#4065804

I need to add xml data stored in multiple rows in a table to a XML document by using XML.modify

When I use the following code it is throwing an error:

DECLARE @PKS       NVARCHAR(MAX) = ''
DECLARE @TABLENAME VARCHAR(100)  = 'CATALOGS'
DECLARE @CRLF      VARCHAR(2)    = CHAR(13) + CHAR(10)
DECLARE @XML       XML  = '<CHANGES/>'
DECLARE @XML_PKS   XML

SELECT @XML.modify('insert (sql:column("PRIMARYKEY")) as last into (/CHANGES)[1]')
  FROM CONFIGURATION_LOG
 WHERE TABLENAME = @TABLENAME
 ORDER BY ID DESC

SELECT @XML

This is giving the following error:

Msg 8137, Level 16, State 1, Line 16

Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

My work around is:

DECLARE @PKS       NVARCHAR(MAX) = ''
DECLARE @TABLENAME VARCHAR(100)  = 'CATALOGS'
DECLARE @CRLF      VARCHAR(2)    = CHAR(13) + CHAR(10)
DECLARE @XML       XML  = '<CHANGES/>'
DECLARE @XML_PKS   XML

SELECT @PKS = @PKS + PRIMARYKEY + @CRLF
  FROM CONFIGURATION_LOG
 WHERE TABLENAME = @TABLENAME
 ORDER BY ID DESC

 SET @XML_PKS =  CONVERT(XML, @PKS)

 SET @XML.modify('insert sql:variable("@XML_PKS") as last into (/CHANGES)[1]')

 SELECT @XML

This works and gives the following output:

<CHANGES>
  <PRIMARYKEY>
    <CATALOGID>101</CATALOGID>
  </PRIMARYKEY>
  <PRIMARYKEY>
    <CATALOGID>103</CATALOGID>
  </PRIMARYKEY>
  <PRIMARYKEY>
    <CATALOGID>102</CATALOGID>
  </PRIMARYKEY>
  <PRIMARYKEY>
    <CATALOGID>101</CATALOGID>
  </PRIMARYKEY>
</CHANGES>

I would like to find an optimized solution. Anyone an idea if the modify statement in a select is allowed?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

The documentation seems clear on this:

Modifies the contents of an XML document. Use this method to modify the content of an xml type variable or column. This method takes an XML DML statement to insert, update, or delete nodes from XML data. The modify() method of the xml data type can only be used in the SET clause of an UPDATE statement.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Harry Drenth Say Hey Kid Points: 678 More actions · Answer 2

Thanks!

I missed that you. I will keep to my origional way.

Have a good day.