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:

RootType	TypeLevel1	TypeLevel2	HotFixID	InstalledBy	InstalledOn
Software	Installed Hotfixes	Hotfix	KB123456	MASTER\SYSTEM	3/17/2025
Software	Installed Hotfixes	Hotfix	KB789012	null	3/19/2025

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:

RootType	TypeLevel1	TypeLevel2	HotFixID	InstalledBy	InstalledOn
Software	Installed Hotfixes	Hotfix	KB123456	MASTER\SYSTEM	3/17/2025
Software	Installed Hotfixes	Hotfix	KB789012	null	3/19/2025

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] = {...}

• sql
• sql-server
• xml
• xquery

Share Improve this question Follow edited Mar 27 at 13:14 Yitzhak Khabinsky 22.5k22 gold badges1919 silver badges2323 bronze badges asked Mar 27 at 9:19 PatrickPatrick 1733 bronze badges 1

• FYI, SSMS is just an IDE (like) application that is used with several products such as (but not limited to) SQL Server, Azure SQL Edge and Azure Synapse. SSMS is likely irrelevant to the question here. What product are you actually using for your data engine? Tagging [ssms] for your data engine is like tagging [visual-studio] for your programming language; it doesn't actually really tell us anything. – Thom A Commented Mar 27 at 12:19

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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

RootType	TypeLevel1	TypeLevel2	HotFixID	InstalledBy	InstalledOn
Software	Installed Hotfixes	Hotfix	KB123456	MASTER\SYSTEM	3/17/2025
Software	Installed Hotfixes	Hotfix	KB7890123	NULL	3/19/2025