Extract information from XML with SQL-CodePudding

I want to extract some data from an XML, but not all the data I want is in the attribute. I need to extract a combination of the subsheetid (feb2a4c6-51d4-424c-b9e9-78b8644678ac) and the name (Clean), preferably in two different columns.

This is the part of the XML from which I want to extract the data. The XML contains multiple subsheets:

<process name="Order Processing">
  <subsheet subsheetid="feb2a4c6-51d4-424c-b9e9-78b8644678ac" type="xx" published="True">
    <name>Clean</name>
    <view>
      <camerax>0</camerax>
      <cameray>0</cameray>
    </view>
  </subsheet>
</process>

I tried the following:

DECLARE @XML --this is the XML as shown above
DECLARE @handle INT  
EXEC sp_xml_preparedocument @handle OUTPUT, @XML

SELECT NULL as [name],*
FROM OPENXML (@handle, '/process/subsheet',1)
    with( 
        subsheetid varchar(100))

this gives the subsheetid when I change the 1 to a 2, it gives me the name:

SELECT name as [name],NULL as [subsheetid]
FROM OPENXML (@handle, '/process/subsheet',2)
    with( 
        name varchar(100))

How can i get both in one line?

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. Also, OPENXML() cannot take advantage of XML indexes while XQuery methods can.

SQL

DECLARE @xml XML = 
N'<process name="Order Processing">
    <subsheet subsheetid="feb2a4c6-51d4-424c-b9e9-78b8644678ac" type="xx" published="True">
        <name>Clean</name>
        <view>
            <camerax>0</camerax>
            <cameray>0</cameray>
        </view>
    </subsheet>
</process>';

SELECT c.value('@subsheetid', 'uniqueidentifier') AS subsheetid
    , c.value('(name/text())[1]', 'VARCHAR(30)') AS [name]
FROM @xml.nodes('/process/subsheet') AS t(c);

Output

 -------------------------------------- ------- 
|              subsheetid              | name  |
 -------------------------------------- ------- 
| FEB2A4C6-51D4-424C-B9E9-78B8644678AC | Clean |
 -------------------------------------- -------