CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2008sqlxml
DrGriff
DrGriff

Reputation: 4916

SQL Server read xml values as empty strings

I have the following c# class

public class Data
{
    public string A {get;set;}
    public string B {get;set;}
    public string C {get;set;}
}

I create an instance of this in my code:

var d = new Data { A = "abc", B = string.Empty, C = null};

I need to convert this to XML and pass it to a sproc in SQL Server 2008.

I do this using:

var xml = new XElement("Data",
    new XElement("A", d.A),
    new XElement("B", d.B),
    new XElement("C", d.C));

The resultant XML in the sproc is:

<Data>
    <A>abc</A>
    <B></B>
    <C />
</Data>

So, there's a difference between an empty string an a null value.

Then in SQL, I'm using the following syntax to read the XML:

INSERT INTO #myTable
SELECT 
  nref.value('(A/text())[1]', 'uniqueidentifier') [A],
  nref.value('(B/text())[1]', 'uniqueidentifier') [B],
  nref.value('(C/text())[1]', 'uniqueidentifier') [C],
FROM @DataXml.nodes('/Data') AS R(nref);

But this is providing me with B and C both as NULL, where B should be empty string.

How can I adapt this to ensure that nulls remain as nulls and empty strings remain as empty strings?

Thanks

Upvotes: 2

Views: 2611

Answers (1)

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

XElement constructor may treat NULL and empty differently, but the XML spec considers < X /> and < X>< /X> to be identical. SQL server XQuery treats them identically as well.

If you want SQL server to distinguish between empty & null elements, you need to exclude the null element completely when you construct the XML in your C# code. For example, the below treats element B as empty & element C as NULL, since it's missing:

declare @x xml = 
'<Data><A>abc</A><B></B></Data>'

SELECT 
  nref.value('(A/text())[1]', 'varchar') [A],
  isnull(nref.value('(B/text())[1]', 'varchar'), case when nref.exist('./B') = 1 then '' end) [B],
  isnull(nref.value('(C/text())[1]', 'varchar'), case when nref.exist('./C') = 1 then '' end) [C]
FROM @x.nodes('/Data') AS R(nref);

Upvotes: 2

Related Questions