Как я могу получить дочерний атрибут родительский элемент

#sql-server #openxml #sql-server-openxml

Вопрос:

Я хотел бы написать хранимую процедуру SQL Server для анализа XML-данных для извлечения значений:

 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

Но в моем результате NU_LIGNE всегда ноль.

Как я могу получить НУ_ЛИНИЮ 1, 2 или 3 соответственно для каждой СТРОКИ ?

Заранее большое спасибо.

1. Почему вы используете эти древние процедуры? SQL Server поддерживает XQUERY с 2005 года…

2. Что касается того, почему, ну, узел CHAMP не имеет значения NU_LINE , поэтому, конечно, он возвращается NULL . NU_LINE находится под узлом ROW .

Ответ №1:

Как я уже упоминал в комментариях, не используйте эти старые процедуры; они предназначены для совместимости с SQL Server 2000. SQL Server поддерживает XQUERY с 2005 года, поэтому нет причин, по которым вы не должны использовать его примерно 16 лет спустя. Если вы сделаете это, все станет намного проще:

 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<>скрипка