SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE CasTable (
    Substance NVARCHAR(MAX),
    UniqueID NVARCHAR(MAX),
    Characteristic NVARCHAR(MAX),
    PhraseID NVARCHAR(MAX),
    Code NVARCHAR(MAX),
    BaseCode NVARCHAR(MAX),
    Description NVARCHAR(MAX),
    Active NVARCHAR(MAX),
    DD NVARCHAR(MAX),
    MM NVARCHAR(MAX),
    YYYY NVARCHAR(MAX)
)
CREATE PROCEDURE LoadCasTableFromXMl @CasXml XML
AS
    INSERT CasTable(Substance, UniqueID, Characteristic, PhraseID, Code, 
                    BaseCode, Description, Active, DD, MM, YYYY)
    SELECT
        'Unknown _substance' AS Substance,
        I.Instance.value('(@UniqueID)[1]', 'NVARCHAR(MAX)') AS UniqueID,
        P.Phrase.value('(@ID)[1]', 'NVARCHAR(MAX)') AS Characteristic,
        C.Child.value('(@Key)[1]', 'NVARCHAR(MAX)') AS PhraseID,
        ISNULL(C.Child.value('(@Code)[1]', 'NVARCHAR(MAX)'), '') AS Code,
        I.Instance.value('(@LitSource)[1]', 'NVARCHAR(MAX)') AS BaseCode,
        C.Child.value('(text())[1]', 'NVARCHAR(MAX)') AS Description,
        I.Instance.value('(@Active)[1]', 'NVARCHAR(MAX)') AS Active,
        I.Instance.value('(@DD)[1]', 'NVARCHAR(MAX)') AS DD,
        I.Instance.value('(@MM)[1]', 'NVARCHAR(MAX)') AS MM,
        I.Instance.value('(@YYYY)[1]', 'NVARCHAR(MAX)') AS YYYY
    FROM @CasXml.nodes('/Specification/Substance/Property/Instance') I(Instance)
    CROSS APPLY I.Instance.nodes('./Fields/Phrases') P(Phrase)
    CROSS APPLY P.Phrase.nodes('./*') C(Child)

-- End of procedure
DECLARE @CasXml XML = N'
<Specification>
  <Substance>
    <Property>
      <Instance DD="31" MM="12" YYYY="2023" Active="True" UniqueID="111" LitSource="AAA">
        <Fields>
          <Phrases ID="1">
            <Child1 Key="Key1" Code="Code1">Description1</Child1>
            <Child2 Key="Key2">Description2</Child2>
          </Phrases>
        </Fields>
      </Instance>
      <Instance DD="01" MM="01" YYYY="2024" Active="False" UniqueID="222" LitSource="BBB">
        <Fields>
          <Phrases ID="1">
            <Child3 Key="Key3" Code="Code3">Description3</Child3>
          </Phrases>
        </Fields>
      </Instance>
    </Property>
  </Substance>
</Specification>
'

EXEC LoadCasTableFromXMl @CasXml

SELECT * FROM CasTable


Substance
UniqueID
Characteristic
PhraseID
Code
BaseCode
Description
Active
DD
MM
YYYY


Unknown _substance
111
1
Key1
Code1
AAA
Description1
True
31
12
2023

Unknown _substance
111
1
Key2

AAA
Description2
True
31
12
2023

Unknown _substance
222
1
Key3
Code3
BBB
Description3
False
01
01
2024

Substance	UniqueID	Characteristic	PhraseID	Code	BaseCode	Description	Active	DD	MM	YYYY
Unknown _substance	111	1	Key1	Code1	AAA	Description1	True	31	12	2023
Unknown _substance	111	1	Key2		AAA	Description2	True	31	12	2023
Unknown _substance	222	1	Key3	Code3	BBB	Description3	False	01	01	2024