Get value by passing dynamic node name of XML

I want get value of node by passing node name dynamically.

I am using this xml:

DECLARE @xml AS XML = '<AuditMsg>
  <SourceDb>TestDatabase</SourceDb>
  <SourceTable>Person</SourceTable>
  <PKFieldName>ID</PKFieldName>
  <UserId>sa</UserId>
  <DMLType>I</DMLType>
  <OldData />
  <ChangedData>
    <t xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <ID>4</ID>
      <FirstName>name 4</FirstName>
      <LastName>surname 4</LastName>
      <DateOfBirth>444444</DateOfBirth>
    </t>
  </ChangedData>
</AuditMsg>'

INSERT INTO @changed
    SELECT
        changed.col.value('local-name(.)', 'VARCHAR(100)') AS Name,
        changed.col.value('.[1]', 'VARCHAR(14)') AS Value,
        ROW_NUMBER() OVER (PARTITION BY changed.col.value('local-name(.)', 'VARCHAR(100)') ORDER BY (SELECT 0)) AS RecordId,
        changed.col.value('(/t/ID)[1]', 'INT') AS ApplicationID
    FROM 
        @ChangedData CD
    CROSS APPLY 
        CD.ChangedData.nodes('/t/*') AS changed(col)

At the 6th line I am passing node name statically as ID. I want to pass that node name dynamically I am trying to do like

DECLARE @Attribute varchar(100) = 'ID'

INSERT INTO @changed
    SELECT
        changed.col.value('local-name(.)', 'VARCHAR(100)') AS Name,
        changed.col.value('.[1]', 'VARCHAR(14)') AS Value,
        ROW_NUMBER() OVER (PARTITION BY changed.col.value('local-name(.)', 'VARCHAR(100)') ORDER BY (SELECT 0)) AS RecordId,
        changed.col.value('(/t/' + @Attribute +')[1]', 'INT') AS ApplicationID
    FROM 
        @ChangedData CD
    CROSS APPLY 
        CD.ChangedData.nodes('/t/*') AS changed(col)

By passing node name dynamically I am getting this error (The argument 1 of the XML data type method "value" must be a string literal)

How can I achieve this?

Solution

Are you looking for something along this?

DECLARE @xml AS XML = 
'<AuditMsg>
  <SourceDb>TestDatabase</SourceDb>
  <SourceTable>Person</SourceTable>
  <PKFieldName>ID</PKFieldName>
  <UserId>sa</UserId>
  <DMLType>I</DMLType>
  <OldData />
  <ChangedData>
    <t xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <ID>4</ID>
      <FirstName>name 4</FirstName>
      <LastName>surname 4</LastName>
      <DateOfBirth>444444</DateOfBirth>
    </t>
  </ChangedData>
</AuditMsg>'

--your externally defined variable

DECLARE @Attribute varchar(100) = 'ID'

--The query

SELECT @xml.value('(/AuditMsg
                    /ChangedData
                    /t
                    /*[local-name()=sql:variable("@Attribute")]
                    /text())[1]','nvarchar(max)');

The XPath will dive down into <t> and find the element, where the element's name equals your parameter passed in via sql:variable(). Within this element we pick the text() node.