How to get all possible values of a xml node using openXML?-CodePudding

How to get all values of cell which are not fixed. could be exists n numbers of time. The below query result 100, it should give 100, 200.

DECLARE @XML AS XML= '<Rows>
  <cells>
    <cell>100</cell>
     <cell>200</cell>
     </cells>
  </Rows>'

    DECLARE @hDoc AS INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT * FROM OPENXML(@hDoc, '/Rows/cells',2) 
With(cell int)) 


EXEC sp_xml_removedocument @hdoc;

CodePudding user response：

Microsoft proprietary OPENXML and its companions sp_xml_preparedocument and sp_xml_removedocument are kept just for backward compatibility with the obsolete SQL Server 2000. Their use is diminished just to very few fringe cases. Starting from SQL Server 2005 onwards, it is strongly recommended to re-write your SQL and switch it to XQuery.

SQL

DECLARE @XML AS XML= '<Rows>
  <cells>
    <cell>100</cell>
     <cell>200</cell>
     </cells>
  </Rows>';

SELECT c.value('.', 'INT') AS cell
FROM @XML.nodes('/Rows/cells/cell/text()') AS t(c);

Output

 ------ 
| cell |
 ------ 
|  100 |
|  200 |
 ------