lundi 29 juin 2015

OPENXML in Sql server

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