How can I get children attribute + parent element

Question

I would like to write a SQL Server stored procedure to parse XML data to retrieve the values:

DECLARE @Champs TABLE 
                (
                    NU_LINE VARCHAR(4), 
                    "KEY" VARCHAR(200), 
                    LABEL VARCHAR(200), 
                    API_NAME VARCHAR(200), 
                    API_FIELD VARCHAR(200), 
                    "VALUE" VARCHAR(MAX)
                );  

DECLARE @docHandle int;  
DECLARE @xmlDocument nvarchar(max); -- or xml type  

SET @xmlDocument = N'<STRUCTURE>
  <GRILLE>
    <ROWS>
      <ROW>
        <NU_LINE>1</NU_LINE>
        <CHAMPS>
          <CHAMP KEY="NUMBER" LABEL="AAA" API_NAME="AAA" API_FIELD="BBB" VALUE="ABC123" />
          <CHAMP KEY="RES" LABEL="AAA" API_NAME="AAA" API_FIELD="BBB" VALUE="JAMES" />
          <CHAMP KEY="DATE_BEGIN" API_NAME="CCC" API_FIELD="BBB" VALUE="14/08/2021" />
          <CHAMP KEY="DATE_END" API_NAME="CCC" API_FIELD="BBB" VALUE="28/08/2021" />
         </CHAMPS>
      </ROW>
      <ROW>
        <NU_LINE>2</NU_LINE>
        <CHAMPS>
          <CHAMP KEY="NUMBER" LABEL="ABC" API_NAME="DDD" API_FIELD="EEE" VALUE="CDE345" />
          <CHAMP KEY="DATE_RES" LABEL="CDE" API_NAME="DDD" API_FIELD="EEE" VALUE="06/05/2021" />
          <CHAMP KEY="RES" LABEL="DEF" API_NAME="DDD" API_FIELD="EEE" VALUE="JOHN" />
          <CHAMP KEY="DATE_BEGIN" API_NAME="DDD" API_FIELD="EEE" VALUE="07/08/2021" />
          <CHAMP KEY="DATE_END" API_NAME="DDD" API_FIELD="EEE" VALUE="14/08/2021" />
         </CHAMPS>
      </ROW>
      <ROW>
        <NU_LINE>3</NU_LINE>
        <CHAMPS>
          <CHAMP KEY="NUMBER" LABEL="OOO" API_NAME="FFF" API_FIELD="GGG" VALUE="EFG567" />
          <CHAMP KEY="DATE_RES" LABEL="OOO" API_NAME="FFF" API_FIELD="GGG" VALUE="10/05/2021" />
          <CHAMP KEY="RES" LABEL="OOO" API_NAME="FFF" API_FIELD="GGG" VALUE="JIM" />
          <CHAMP KEY="DATE_BEGIN" API_NAME="FFF" API_FIELD="GGG" VALUE="24/07/2021" />
          <CHAMP KEY="DATE_END" API_NAME="FFF" API_FIELD="GGG" VALUE="31/07/2021" />
         </CHAMPS>
      </ROW>
    </ROWS>
  </GRILLE>
</STRUCTURE>'

EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;  

-- Use OPENXML to provide rowset consisting of customer data.  
INSERT @Champs   
    SELECT *   
    FROM OPENXML(@docHandle, N'/STRUCTURE/GRILLE/ROWS/ROW/CHAMPS/CHAMP') 
    WITH (NU_LINE VARCHAR(4), "KEY" VARCHAR(200), LABEL VARCHAR(200), API_NAME VARCHAR(200), API_FIELD VARCHAR(200), "VALUE" VARCHAR(MAX))

SELECT * FROM @Champs

But in my result NU_LIGNE is always null.

How can I do to get the NU_LINE 1, 2 or 3 respectively for each ROW ?

Thanks a lot in advance.

Answer 1

As I mentioned in the comments, don't use those old procedures; they are there for compatibility for SQL Server 2000. SQL Server has supported XQUERY since 2005, so there's no reason why you shouldn't be using it some 16 years later. If you do that, this is much more simple:

DECLARE @xmlDocument  xml; 
SET @xmlDocument = N'<STRUCTURE>
  <GRILLE>
    <ROWS>
      <ROW>
        <NU_LINE>1</NU_LINE>
        <CHAMPS>
          <CHAMP KEY="NUMBER" LABEL="AAA" API_NAME="AAA" API_FIELD="BBB" VALUE="ABC123" />
          <CHAMP KEY="RES" LABEL="AAA" API_NAME="AAA" API_FIELD="BBB" VALUE="JAMES" />
          <CHAMP KEY="DATE_BEGIN" API_NAME="CCC" API_FIELD="BBB" VALUE="14/08/2021" />
          <CHAMP KEY="DATE_END" API_NAME="CCC" API_FIELD="BBB" VALUE="28/08/2021" />
         </CHAMPS>
      </ROW>
      <ROW>
        <NU_LINE>2</NU_LINE>
        <CHAMPS>
          <CHAMP KEY="NUMBER" LABEL="ABC" API_NAME="DDD" API_FIELD="EEE" VALUE="CDE345" />
          <CHAMP KEY="DATE_RES" LABEL="CDE" API_NAME="DDD" API_FIELD="EEE" VALUE="06/05/2021" />
          <CHAMP KEY="RES" LABEL="DEF" API_NAME="DDD" API_FIELD="EEE" VALUE="JOHN" />
          <CHAMP KEY="DATE_BEGIN" API_NAME="DDD" API_FIELD="EEE" VALUE="07/08/2021" />
          <CHAMP KEY="DATE_END" API_NAME="DDD" API_FIELD="EEE" VALUE="14/08/2021" />
         </CHAMPS>
      </ROW>
      <ROW>
        <NU_LINE>3</NU_LINE>
        <CHAMPS>
          <CHAMP KEY="NUMBER" LABEL="OOO" API_NAME="FFF" API_FIELD="GGG" VALUE="EFG567" />
          <CHAMP KEY="DATE_RES" LABEL="OOO" API_NAME="FFF" API_FIELD="GGG" VALUE="10/05/2021" />
          <CHAMP KEY="RES" LABEL="OOO" API_NAME="FFF" API_FIELD="GGG" VALUE="JIM" />
          <CHAMP KEY="DATE_BEGIN" API_NAME="FFF" API_FIELD="GGG" VALUE="24/07/2021" />
          <CHAMP KEY="DATE_END" API_NAME="FFF" API_FIELD="GGG" VALUE="31/07/2021" />
         </CHAMPS>
      </ROW>
    </ROWS>
  </GRILLE>
</STRUCTURE>';

SELECT R.R.value('(NU_LINE/text())[1]','int') AS NU_LINE,
       C.C.value('@KEY','varchar(200)') AS [KEY],
       C.C.value('@LABEL','varchar(200)') AS [LABEL],
       C.C.value('@API_NAME','varchar(200)') AS [API_NAME],
       C.C.value('@API_FIELD','varchar(200)') AS [API_FIELD],
       C.C.value('@VALUE','varchar(MAX)') AS [VALUE]
FROM @xmlDocument.nodes('/STRUCTURE/GRILLE/ROWS/ROW')R(R)
     CROSS APPLY R.R.nodes('CHAMPS/CHAMP')C(C);

db<>fiddle