Xml with null Column

How to Retrieve Null Values from an XML Column

XML Basics

Some key points to remember when working with XML:

• XML tags are case-sensitive; make sure the opening and closing tags match in case.
• Elements in XML must be correctly nested.
• Attribute values should always be enclosed in quotes.
• The attribute xsi:nil is used to represent a null value in XML.

Special Characters in XML:

<message>salary < 1000</message>
&lt; < less than
&gt; > greater than
&amp; & ampersand 
&apos; ' apostrophe
&quot; " quotation mark

Example SQL Query:

select name, object_id, principal_id from sys.tables for XML PATH, ELEMENTS XSINIL;

Sample XML Output:

object_id	principal_id	name
117575457	NULL	spt_fallback_db
133575514	NULL	spt_fallback_dev
149575571	NULL	spt_fallback_usg
1803153469	NULL	spt_monitor
2107154552	NULL	MSreplication_options

SQL to Parse XML with Null Values:

declare @xml xml
select @xml = N'<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_db</name>
  <object_id>117575457</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_dev</name>
  <object_id>133575514</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_usg</name>
  <object_id>149575571</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_monitor</name>
  <object_id>1803153469</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>MSreplication_options</name>
  <object_id>2107154552</object_id>
  <principal_id xsi:nil="true" />
</row>'
; 
with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as xsi)
select  N.C.value('object_id[1]', 'int') as object_id,
        N.C.value('principal_id[1][not(@xsi:nil = "true")]', 'int') as principal_id,
        N.C.value('name[1]', 'varchar(100)') as name
from @xml.nodes('//row') N(C)

For Additional Information:

You can refer to the following article for more details: XML Type Using Query

Escape XML special characters in SQL Server Query

In this post, I'll demonstrate how to handle special characters in SQL Server.

DECLARE @xml XML = '<zzz xmlns="http://himanshupatel.in"><aaa>aaa</aaa> <bbb>param1=xyz&amp;para2=dasdasdfdas&amp;param3</bbb></zzz>'

SELECT @xml [before], DATALENGTH(@xml) dl

;WITH XMLNAMESPACES (DEFAULT 'http://himanshupatel.in')
SELECT @xml.value('(zzz/bbb/text())[1]', 'VARCHAR(100)')

Here’s an image that illustrates the output when using special characters in the XML.

SELECT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc,
    (SELECT m.definition FOR XML PATH(''), type) AS Object_Definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

SELECT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc,
    (SELECT m.definition FOR XML PATH('')) AS Object_Definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

Here’s the output when you include the 'type' keyword in the query:

Now, here’s the result without the 'type' keyword:

select (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''), type )

select (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''))

select stuff((select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''), type).value('(./text())[1]', 'varchar(max)'), 1, 2, '') as namelist;

Here’s the output of the three queries above:

For further reference, check out the following resources:

Using XML Type in SQL Queries

XML with Null Columns

How to read the SQL Server query plan file using TSQL

 How to read the SQL Server query plan file using T-SQL

In this post, I'll explain how to read an SQL Server execution query plan from the SSMS query file.

DECLARE @plancontents xml, @xml XML
SET @plancontents = (SELECT * FROM OPENROWSET(BULK 'P:\\Users\\hpatterson\\Documents\\myplan.sqlplan', SINGLE_CLOB) x)
-- Remove unnecessary namespace
-- SET @plancontents = REPLACE(@plancontents, 'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"', '')
-- Store the cleaned content into the XML variable
SET @xml = CAST(@plancontents AS XML)

-- You can also get the query plan directly like this:
-- SELECT @xml = query_plan FROM sys.dm_exec_query_plan
SELECT @xml

-- Notice the removal of the xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan attribute

-- Now, let's process all the execution plan nodes and extract relevant details
WITH xmlnamespaces (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@AvgRowSize', 'nvarchar(max)') AS AvgRowSize,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@Parallel', 'nvarchar(max)') AS Parallel,
    c.value('.[1]/@EstimateRebinds', 'nvarchar(max)') AS EstimateRebinds,
    c.value('.[1]/@EstimateRewinds', 'nvarchar(max)') AS EstimateRewinds,
    c.value('.[1]/@NodeId', 'nvarchar(max)') AS NodeId,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') AS EstimatedTotalSubtreeCost,
    c.value('.[1]/@EstimateRows', 'nvarchar(max)') AS EstimateRows,
    c.value('.[1]/@EstimateIO', 'nvarchar(max)') AS EstimateIO,
    c.value('.[1]/@EstimateCPU', 'nvarchar(max)') AS EstimateCPU,
    -- This will return the node XML for easier inspection
    c.query('.') AS ExecPlanNode        
FROM 
    -- Only returns nodes with the name RelOp, including those that are nested
    @xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC

How to work with xml type using Query

Working with OPENXML in SQL Server

OPENXML is a Transact-SQL keyword that allows you to treat XML data stored in memory as if it were a relational table or view. This functionality is especially helpful when importing or transforming XML content into tabular format.

To use OPENXML, the sp_xml_preparedocument procedure is first called to parse the XML and return a handle to the document, which can then be referenced in SQL queries.

Example: Populating Tables Using OPENXML

-- Create necessary tables
CREATE TABLE Customers (
    CustomerID varchar(20) PRIMARY KEY,
    ContactName varchar(20),
    CompanyName varchar(20)
);

CREATE TABLE Orders (
    CustomerID varchar(20),
    OrderDate datetime
);

-- Declare XML and handle
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max);

-- Set XML data
SET @xmlDocument = N'
<ROOT>
  <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
    <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00" />
    <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00" />
  </Customers>
  <Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">
    No Orders yet!
  </Customers>
</ROOT>';

-- Prepare the XML document
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Insert customers
INSERT INTO Customers
SELECT * FROM OPENXML(@docHandle, '/ROOT/Customers') WITH Customers;

-- Insert orders
INSERT INTO Orders
SELECT * FROM OPENXML(@docHandle, '//Orders') WITH Orders;

-- Query with OPENXML
SELECT * FROM OPENXML(@docHandle, '/ROOT/Customers/Orders')
  WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);

-- Clean up the XML handle
EXEC sp_xml_removedocument @docHandle;

Example: Using nodes() Method

SELECT 
    nref.value('(first-name/text())[1]', 'nvarchar(50)') AS FirstName,
    nref.value('(last-name/text())[1]', 'nvarchar(50)') AS LastName
FROM T
CROSS APPLY xCol.nodes('//author') AS R(nref)
WHERE nref.exist('first-name[. != "David"]') = 1;

Looping Through XML Using OPENXML()

DECLARE name_cursor CURSOR FOR 
    SELECT xCol FROM T;

OPEN name_cursor;

DECLARE @xmlVal XML;
DECLARE @idoc int;

FETCH NEXT FROM name_cursor INTO @xmlVal;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal;

    SELECT * 
    FROM OPENXML(@idoc, '//author') 
    WITH (
        FirstName varchar(50) 'first-name',
        LastName varchar(50) 'last-name'
    ) R
    WHERE R.FirstName != 'David';

    EXEC sp_xml_removedocument @idoc;

    FETCH NEXT FROM name_cursor INTO @xmlVal;
END

CLOSE name_cursor;
DEALLOCATE name_cursor;

Example XML Structure

<Customers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <Customer>
       <ItemId>1001</ItemId>
       <Value>Mr Patel</Value>
   </Customer>
   <Customer>
      <ItemId>2002</ItemId>
      <Value>Mr Bhatt</Value>
   </Customer>
</Customers>

Extracting Data from XML

SELECT
    Cst.value('(ItemId)[1]', 'int') AS ItemID,
    Cst.value('(Value)[1]', 'Varchar(50)') AS CustomerName
FROM dbo.Sales.CustomerList.nodes('/Customers/Customer') AS A(Cst);

Using CROSS APPLY with XML

CREATE TABLE Sales2020 (ID int, CustomerList xml);

INSERT INTO Sales2020
SELECT 1, '
    <Customers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <Customer>
           <ItemId>1001</ItemId>
           <Value>Mr Patel</Value>
       </Customer>
       <Customer>
          <ItemId>2</ItemId>
          <Value>Mr Bhatt</Value>
       </Customer>
    </Customers>';

SELECT
   N.C.value('ItemId[1]', 'int') AS ItemId,
   N.C.value('Value[1]', 'varchar(100)') AS Value
FROM Sales2020
CROSS APPLY CustomerList.nodes('//Customer') AS N(C);

References

• XML with Null Column
• Tables with XML Columns
• OPENXML in Microsoft Docs