How to handle not always existing node while reading from XML

I'm reading on SQL Server from a column in a table which is of type XML.
The problem is that there is a node called "InstalledBy" which does not always exist.

The definition of the table is like that:

CREATE TABLE [MyDatabase].[dbo].[MyTable](
    [Id] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [FileGuid] [varchar](36) NOT NULL,
    [XmlFileContent] [xml] NOT NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The part including the problematic node looks like that:

<SDCAsset Type="Software">
  <SDCAsset Type="Installed Hotfixes">
    <SDCAsset Type="Hotfix" Description="Installed Hotfixes" MaterialNumber="UID" SerialNumber="KB123456">
      <Item Name="type" Valuetype="xs:string">Hotfix</Item>
      <Item Name="key" Valuetype="xs:string">ad:HotFixID</Item>
      <Item Name="HotFixID" Valuetype="xs:string">KB123456</Item>
      <Item Name="InstalledBy" Valuetype="xs:string">MASTER\SYSTEM</Item>
      <Item Name="InstalledOn" Valuetype="xs:date">3/17/2025</Item>
    </SDCAsset>
    <SDCAsset Type="Hotfix" Description="Installed Hotfixes" MaterialNumber="UID" SerialNumber="KB7890123">
      <Item Name="type" Valuetype="xs:string">Hotfix</Item>
      <Item Name="key" Valuetype="xs:string">ad:HotFixID</Item>
      <Item Name="HotFixID" Valuetype="xs:string">KB789012</Item>
      <Item Name="InstalledOn" Valuetype="xs:date">3/19/2025</Item>
    </SDCAsset>
  </SDCAsset>
</SDCAsset>

What I want to get is a resulttable like this:

I tried the exist function and can now see if the InstalledBy-node exists or not.
But how can i get the node value and so the resulttable I want?

This is the Select I have so far:

SELECT
    [FileGuid]
    ,[RootType] = roottype.value('@Type', 'nvarchar(max)')
    ,[TypeLevel1] = typelevel1.value('@Type', 'nvarchar(max)')
    ,[TypeLevel2] = typelevel2.value('@Type', 'nvarchar(max)')
    ,ISNULL([hotfixid].value('.','nvarchar(max)'), null) AS [HotFixID]
    --,ISNULL([installedby].value('.','nvarchar(max)'), null) AS [InstalledBy]
    ,typelevel2.exist('./Item[@Name="InstalledBy"]') AS [InstalledByExists] --1 if node with Name="InstalledBy" exists, 0 if it not exists
    --,CASE
    --   WHEN typelevel2.exist('./Item[@Name="InstalledBy"]') = 1 THEN ISNULL([installedby].value('.','nvarchar(max)'), null)
    --   ELSE ''
    -- END AS [InstalledBy]
    ,ISNULL([installedon].value('.','nvarchar(max)'), null) AS [InstalledOn]
FROM [MyDatabase].[dbo].[MyTable] WITH (NOLOCK)
OUTER APPLY [XmlFileContent].nodes('/rootSDCAsset/SDCAsset') AS T2(roottype)
OUTER APPLY [roottype].nodes('SDCAsset') AS T3(typelevel1)
OUTER APPLY [typelevel1].nodes('SDCAsset') AS T4(typelevel2)
OUTER APPLY [typelevel2].nodes('Item') AS T7(hotfixid)
--OUTER APPLY [typelevel2].nodes('Item') AS T8(installedby)
OUTER APPLY [typelevel2].nodes('Item') AS T9(installedon)
WHERE roottype.value('@Type', 'nvarchar(max)') = 'Software'
AND   typelevel1.value('@Type', 'nvarchar(max)') = 'Installed Hotfixes'
AND   typelevel2.value('@Type', 'nvarchar(max)') = 'Hotfix'
AND   hotfixid.value('@Name', 'nvarchar(max)') = 'HotFixID'
--AND   installedby.value('@Name', 'nvarchar(max)') = 'InstalledBy'
AND   installedon.value('@Name', 'nvarchar(max)') = 'InstalledOn'
AND   [FileGuid] = {...}

Please try the following solution.

If you need a blank space instead of NULL, you can wrap it as follows:

COLEASCE(t2.c.value(...), '') AS InstalledBy

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY,XmlFileContent XML);
INSERT INTO @tbl (XmlFileContent) VALUES
(N'<SDCAsset Type="Software">
  <SDCAsset Type="Installed Hotfixes">
    <SDCAsset Type="Hotfix" Description="Installed Hotfixes" MaterialNumber="UID" SerialNumber="KB123456">
      <Item Name="type" Valuetype="xs:string">Hotfix</Item>
      <Item Name="key" Valuetype="xs:string">ad:HotFixID</Item>
      <Item Name="HotFixID" Valuetype="xs:string">KB123456</Item>
      <Item Name="InstalledBy" Valuetype="xs:string">MASTER\SYSTEM</Item>
      <Item Name="InstalledOn" Valuetype="xs:date">3/17/2025</Item>
    </SDCAsset>
    <SDCAsset Type="Hotfix" Description="Installed Hotfixes" MaterialNumber="UID" SerialNumber="KB7890123">
      <Item Name="type" Valuetype="xs:string">Hotfix</Item>
      <Item Name="key" Valuetype="xs:string">ad:HotFixID</Item>
      <Item Name="HotFixID" Valuetype="xs:string">KB789012</Item>
      <Item Name="InstalledOn" Valuetype="xs:date">3/19/2025</Item>
    </SDCAsset>
  </SDCAsset>
</SDCAsset>');
-- DDL and sample data population, end

SELECT t.c.value('@Type', 'NVARCHAR(128)') AS RootType
    , t1.c.value('@Type', 'NVARCHAR(128)') AS TypeLevel1
    , t2.c.value('@Type', 'NVARCHAR(128)') AS TypeLevel2
    , t2.c.value('@SerialNumber', 'NVARCHAR(128)') AS HotFixID
    , t2.c.value('(Item[@Name="InstalledBy"]/text())[1]', 'NVARCHAR(128)') AS InstalledBy
    , t2.c.value('(Item[@Name="InstalledOn"]/text())[1]', 'NVARCHAR(128)') AS InstalledOn
FROM @tbl
OUTER APPLY XmlFileContent.nodes('/SDCAsset') AS t(c)
OUTER APPLY t.c.nodes('SDCAsset') AS t1(c)
OUTER APPLY t1.c.nodes('SDCAsset') AS t2(c);

Output