SQL parse xml as table



I have some xml like:



<MyDetails>
<detail key="key1" value="value1" />
<detail key="key2" value="value2" />
<detail key="key3" value="value3" />
<detail key="key4" value="value4" />
</MyDetails>


And I want to be able to parse it in a table format of two columns Key, and Value. How can I create a SQL function to achieve this by specifying a Node path i.e. in this case '/MyDetails/detail' and KeyAttributeName and ValueAttributeName. I created the following function but it gives me the error:



CREATE FUNCTION GetXmlTable (
@XmlSource XML
,@HierarchyPath NVARCHAR(50)
,@SpecificKey NVARCHAR(255) = NULL
,@KeyAttributeName NVARCHAR(50) = 'key'
,@ValueAttributeName NVARCHAR(50) = 'value'
)
RETURNS TABLE
AS
RETURN
(
SELECT XmlElement.Attribute.value('@' + @KeyAttributeName, 'nvarchar(255)') AS [Key]
,XmlElement.Attribute.value('@' + @ValueAttributeName, 'nvarchar(500)') AS [Value]
FROM @XmlSource.nodes('/' + @HierarchyPath) AS XmlElement(Attribute)
where @SpecificKey IS NULL OR XmlElement.Attribute.value('@' + @KeyAttributeName, 'nvarchar(255)') = @SpecificKey
)

GO


Error:



Msg 8172, Level 16, State 1, Procedure GetXmlTable, Line 12 The argument 1 of the XML data type method "nodes" must be a string literal.



Thanks in advance


No comments:

Post a Comment