SQL query XML and return null if node is not existing-CodePudding

I have xml files where a node (field2) is not always present. If the node is not present i want to get null value.

SELECT 
    field1, field2 
FROM 
    (SELECT DISTINCT 
         xmlField1.value('text()[1]','VARCHAR(256)') AS field1, 
         xmlField2.value('text()[1]','VARCHAR(256)') AS field2 
     FROM 
         [dbo].XMLTable t 
     CROSS APPLY 
         [XMLData].nodes('/collection/test/field1') A(xmlField1)
     CROSS APPLY 
         [XMLData].nodes('/collection/test/field2') B(xmlField2)) xmlData

This query works if I have both fields in the XML file, but if field2 is missing, I don't get any values returned.

CodePudding user response：

A minimal reproducible example is not provided.

Please try the following conceptual example.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY(1,1) PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<collection>
    <test>
        <field1>fld11</field1>
        <field2>fld12</field2>
    </test>
    <test>
        <field1>fld12</field1>
    </test>
    <test>
        <field1>fld13</field1>
        <field2/>
    </test>
</collection>');
-- DDL and sample data population, end

SELECT ID
    , c.value('(field1/text())[1]','VARCHAR(256)') AS field1
    , c.value('(field2/text())[1]','VARCHAR(256)') AS field2
FROM @tbl
    CROSS APPLY xmldata.nodes('/collection/test') AS t(c);

Output

 ---- -------- -------- 
| ID | field1 | field2 |
 ---- -------- -------- 
|  1 | fld11  | fld12  |
|  1 | fld12  | NULL   |
|  1 | fld13  | NULL   |
 ---- -------- --------

CodePudding user response：

It's likely because your CROSS APPLY is doing a compare with field1 and field2. The compare operator is likely = (equal), which means a record with a NULL value will be eliminated. I don't use CROSS APPLY very often, so I'm not sure if you can, but have you tried using isnull([XMLData].nodes('/collection/test/field2'), '') B(xmlField2) in the CROSS APPLY?

If you supply sample data, it will be easier to test this solution.