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