SQL XML Query with xpath defined in table



I am trying to extract some data from XML using SQL Query, i am having issue in getting values if XPath is defined in a table and using that in Query. Here is the example



DECLARE @StudentData XML
SET @StudentData ='<StudentData xmlns:xsd="http://ift.tt/tphNwY" xmlns:xsi="http://ift.tt/ra1lAU">
<Properties>
<Property>
<Name>RollNumber</Name>
<Value>2127</Value>
</Property>
<Property>
<Name>SudentName</Name>
<Value>ABCD</Value>
</Property>
<Property>
<Name>Subject1</Name>
<Value>80</Value>
</Property>
</Properties>
</StudentData>';
CREATE TABLE #PropertyPath (
[XPath] VARCHAR (2000) NOT NULL
);

INSERT INTO #PropertyPath
SELECT '(/StudentData/Properties/Property[Name=''RollNumber'']/Value)[1]'
UNION
SELECT '(/StudentData/Properties/Property[Name=''SudentName'']/Value)[1]'

SELECT @StudentData.value('(/*[local-name() = sql:column("[XPath]")])[1]','VARCHAR(100)'),XPath
FROM #PropertyPath

SELECT @StudentData.value('(/StudentData/Properties/Property[Name=''RollNumber'']/Value)[1]','VARCHAR(100)'),
@StudentData.value('(/StudentData/Properties/Property[Name=''SudentName'']/Value)[1]','VARCHAR(100)')
FROM @StudentData.nodes('/') as x(t)

DROP TABLE #PropertyPath


Am i doing anything wrong or is it not possible?


No comments:

Post a Comment