Dynamic XPath Creation in SQL Server

Question

I have an XML and I want to get the Xml Node values dynamically. I want to Pass the Node Name and the in return the function or SP should return me the value of that Node.

<ClassificationTypeEntity>

        <LTABLE_ID>3170</LTABLE_ID>

        <LTABLE_CODE>script Code</LTABLE_CODE>

        <LTABLE_DESC>alert(''hello'')</LTABLE_DESC>

        <ACTIVE_YES_NO>1</ACTIVE_YES_NO>enter code here

        <PRIVATE_FILING>0</PRIVATE_FILING>

        <RETENTION_CODE /><RETENTION_TITLE />

        <LTABLE_ID_P>0</LTABLE_ID_P>

</ClassificationTypeEntity>;

For Example, In the above xml, when I want the value of LTABLE_CODE Node, I will pass the same and the result should I get is

script code

Same for LTABLE_DESC the result should be alert(''hello'')

However I can write the 2 Xpath query for both but in case my schema gets changed (more properties added or removed) then I will have to change my SP also.

Thanks, Mohit Jain

As long as your node names aren't changing, you could use //LTABLE_CODE and //LTABLE_DESC — JWiley
– JWiley, Commented Jan 17, 2013 at 14:10

Andrew Morton · Accepted Answer · 2017-05-05 09:12:28Z

1

Thanks for your replies.

I have found the solution:

declare @xmlData xml, @NodeName VarChar(500), @nodeValue VarChar(500)

Set @NodeName = 'LTABLE_CODE';

--Set @NodeName = 'LTABLE_DESC';

set @xmlData = 

 '<ClassificationTypeEntity xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

   <LTABLE_ID>3170</LTABLE_ID>

   <LTABLE_CODE>&lt;script&gt;al</LTABLE_CODE>

   <LTABLE_DESC>&lt;script&gt;alert(''hello'')&lt;/script&gt;</LTABLE_DESC>

   <ACTIVE_YES_NO>1</ACTIVE_YES_NO>

   <PRIVATE_FILING>0</PRIVATE_FILING>

   <RETENTION_CODE /><RETENTION_TITLE />

   <LTABLE_ID_P>0</LTABLE_ID_P>

</ClassificationTypeEntity>'

SET @nodeValue = @xmlData.value('(/ClassificationTypeEntity/*[local-name()=sql:variable("@NodeName")])[1]','nvarchar(max)')

SELECT @nodeValue

edited May 5, 2017 at 9:12

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

answered Jan 18, 2013 at 14:18

Mohit Jain

611 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic XPath Creation in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related