Missing elements in T-SQL OPENXML Query

Question

Context: I have an XML document with the structure below that I am querying in SQL Server via OPENXML. I'm able to query almost all elements in the document except for several child elements that OPENXML is not capturing for some reason.

<HRD>
<Search>ER-0002</Search>
<SubCHD>
<APPR_PROC_CD>ADR</APPR_PROC_CD>
<Line_Items>
<APPRVL_EMPL_ID>119845</APPRVL_EMPL_ID>
<APPRVL_SEQ_NO>23358960</APPRVL_SEQ_NO>
<APPRVL_DTT>2019-18-05T13:19:27</APPRVL_DTT>
</Line_Items>
</Line_Items>
<APPRVL_EMPL_ID>788270</APPRVL_EMPL_ID>
<APPRVL_SEQ_NO>287360</APPRVL_SEQ_NO>
<APPRVL_DTT>2014-11-05T13:19:27</APPRVL_DTT>
</Line_Items>
</Line_Items>
<APPRVL_EMPL_ID>72987437</APPRVL_EMPL_ID>
<APPRVL_SEQ_NO>23484580</APPRVL_SEQ_NO>
<APPRVL_DTT>2013-11-05T13:19:27</APPRVL_DTT>
<Line_Items>
<RQ_Sub>
<Delta>N</Delta>
<LN_Act>
<ACCT_ID>ABDSNJD1267</ACCT_ID>
</LN_Act>
</RQ_Sub>
</SubCHD>
</HRD>

Questions: Can someone please explain what logic I need to add/change in the SQL query to capture all the data within each of the 3 Line_Items child elements? The query below only returns the first one. I'm expecting to get the full contents of the XML in tabular form, similar to screenshot below but with additional rows for missing Line_Items elements.

EXEC sp_xml_removedocument @reqid_xml_doc


DECLARE @reqid_xml_data XML

SELECT @reqid_xml_data=O
FROM OPENROWSET(BULK N'C:\Users\eb\Desktop\Important_Docs_Links\Important_Documents\req_status_xml_data.xml', SINGLE_BLOB) as file_output(O)

DECLARE @reqid_xml_doc int

EXEC sp_xml_preparedocument @reqid_xml_doc OUTPUT, @reqid_xml_data

SELECT *
FROM OPENXML(@reqid_xml_doc,'HRD/*',2)

WITH (
        APPRVL_EMPL_ID int 'Line_Items/APPRVL_EMPL_ID', 
        APPRVL_SEQ_NO int 'Line_ItemsL/APPRVL_SEQ_NO',
        APPRVL_DTT nvarchar(25)'Line_Items/APPRVL_DTT',

        DELTA nvarchar(15) 'RQ_Sub/DELTA',

        ACCT_ID nvarchar(50) 'RQ_Sub/LN_Act/ACCT_ID'

        )

EXEC sp_xml_removedocument @reqid_xml_doc

Answer 1

Your approach using FROM OPENXML together with the SPs to prepare and to remove a document is outdated and should not be used any more (rare exceptions exist).

For about 15 years SQL-Server supports native XML methods for XPath and XQuery:

Try it like this (I assume, that the content of your file is loaded into @xml):

(Hint: I had to repair some errors... Your XML was not well-formed...)

DECLARE @xml XML=
N'<HRD>
  <Search>ER-0002</Search>
  <SubCHD>
    <APPR_PROC_CD>ADR</APPR_PROC_CD>
    <Line_Items>
      <APPRVL_EMPL_ID>119845</APPRVL_EMPL_ID>
      <APPRVL_SEQ_NO>23358960</APPRVL_SEQ_NO>
      <APPRVL_DTT>2019-18-05T13:19:27</APPRVL_DTT>
    </Line_Items>
    <Line_Items>
      <APPRVL_EMPL_ID>788270</APPRVL_EMPL_ID>
      <APPRVL_SEQ_NO>287360</APPRVL_SEQ_NO>
      <APPRVL_DTT>2014-11-05T13:19:27</APPRVL_DTT>
    </Line_Items>
    <Line_Items>
      <APPRVL_EMPL_ID>72987437</APPRVL_EMPL_ID>
      <APPRVL_SEQ_NO>23484580</APPRVL_SEQ_NO>
      <APPRVL_DTT>2013-11-05T13:19:27</APPRVL_DTT>
    </Line_Items>
    <RQ_Sub>
      <Delta>N</Delta>
      <LN_Act>
        <ACCT_ID>ABDSNJD1267</ACCT_ID>
      </LN_Act>
    </RQ_Sub>
  </SubCHD>
</HRD>'; 

--The query will use some calls of .value() against the variable itself (non-repeating elements) and it will use .nodes() to return repeating elements (your <Line_Items>) as a derived set.

SELECT @xml.value('(/HRD/Search/text())[1]','varchar(100)') AS Search
      ,@xml.value('(/HRD/SubCHD/APPR_PROC_CD/text())[1]','varchar(100)') AS ApprProcCd
      ,li.value('(APPRVL_EMPL_ID/text())[1]','bigint') AS EmplId
      ,li.value('(APPRVL_SEQ_NO/text())[1]','bigint') AS SeqNo
      ,li.value('(APPRVL_DTT/text())[1]','varchar(100)') AS Dtt --Attention!!!!!
      ,@xml.value('(/HRD/SubCHD/RQ_Sub/Delta/text())[1]','varchar(100)') AS Delta
      ,@xml.value('(/HRD/SubCHD/RQ_Sub/LN_Act/ACCT_ID/text())[1]','varchar(100)') AS AcctId
FROM @xml.nodes('/HRD/SubCHD/Line_Items') A(li)

The result

ER-0002 ADR 119845      23358960    2019-18-05T13:19:27 N   ABDSNJD1267
ER-0002 ADR 788270      287360      2014-11-05T13:19:27 N   ABDSNJD1267
ER-0002 ADR 72987437    23484580    2013-11-05T13:19:27 N   ABDSNJD1267

Very important: The date-time-values look as if they were ISO8601 (the T in the middle), but the date format is ydm which must be ymd. Otherwise we could specify datetime as target type in .value().