‫به روز رسانی فیلدهای XML در SQL Server - قسمت دوم

DECLARE @tblTest AS TABLE (xmlField XML)

INSERT INTO @tblTest
(
 xmlField
)
VALUES
(
 '<Sample>
    <Node1>Value1</Node1>
    <Node2>Value2</Node2>
    <Node3>OldValue</Node3>
</Sample>'
)

DECLARE @Name NVARCHAR(50)
SELECT @Name = 'Vahid'

UPDATE @tblTest
SET    xmlField.modify(
        'insert element Node4 {sql:variable("@Name")} as last into
 (/Sample)[1]'
    )

SELECT tt.xmlField
FROM   @tblTest tt

<Sample>
<Node1>Value1</Node1>
<Node2>Value2</Node2>
<Node3>OldValue</Node3>
<Node4>Vahid</Node4>
</Sample>

DECLARE @XML AS XML

SELECT @XML = tt.xmlField
FROM   @tblTest tt

SELECT t.c.value('local-name(..)', 'varchar(max)') AS ParentNodeName,
    t.c.value('local-name(.)', 'varchar(max)') AS NodeName,
    t.c.value('text()[1]', 'varchar(max)') AS NodeText

FROM   @XML.nodes('/*/*') AS t(c)

ParentNodeName - NodeName - NodeText
Sample           Node1     Value1
Sample           Node2     Value2
Sample           Node3     OldValue
Sample           Node4     Vahid