I have a XML structure "1234"
I want get the "Version" values using "OPENXML".
I know it can be done with the below query.
DECLARE @AttachVersions XML
SET @AttachVersions = '<AttachVersion><Version>1</Version><Version>2</Version><Version>3</Version><Version>4</Version></AttachVersion>'
SELECT ParamValues.[Version].value('.', 'VARCHAR(10)') AS [Version] FROM @AttachVersions.nodes('/AttachVersion/Version') as ParamValues([Version])
We can not change the input parameter to XML.
I know there is an alternate way to get the with "OPENXML" if it's an attribute value. Here's the sample code
DECLARE @FileterOptions VARCHAR(MAX)
SET @FileterOptions = '<AttachVersion><Version Value="1" /><Version Value="2" /><Version Value="3" /><Version Value="4" /></AttachVersion>'
DECLARE @AttachVersionHandle INT
CREATE TABLE #tmpAttachVersionList
(
[Value] INT
)
EXEC sp_xml_preparedocument @AttachVersionHandle OUTPUT, @FileterOptions
INSERT #tmpAttachVersionList ( [Value] )
SELECT [Value] FROM OPENXML( @AttachVersionHandle, '//Version' ) WITH #tmpAttachVersionList
SELECT * FROM #tmpAttachVersionList
DROP TABLE #tmpAttachVersionList
Is it possible to get the "Version" values with OPENXML using "XPath"?
Aucun commentaire:
Enregistrer un commentaire