SQL Server read xml values as empty strings

Question

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

Why do you need to convert it to XML to pass to the stored proc, only to break it out of XML back into discrete fields? Just pass the discrete values to the stored proc... — Hannah Vernon
– Hannah Vernon, Commented Sep 23, 2013 at 13:46
Max, yes for "rad" read "read". The XML in this message was very simplistic, my actual XML is far more complicated and allows me to pass a batch of objects to my sproc which can then iterate through them. — DrGriff
– DrGriff, Commented Sep 24, 2013 at 14:49

Kevin Suchlicki · Accepted Answer · 2013-09-24 21:29:01Z

2

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);

edited Sep 24, 2013 at 21:29

answered Sep 23, 2013 at 14:01

Kevin Suchlicki

3,1542 gold badges17 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DrGriff Over a year ago

Your xml node selector does not use the "text()" method and so is likely to return expected results when there are child elements, e.g. when @x = '<Data><A>abc</A><B></B><D><D1>d1</D1><D2>d2</D2></D></Data>'. So when you do use the text() method (e.g. "nref.value('(B/text())[1]', 'varchar(10)') [B]") then values for B and C are both returned as NULL.

Kevin Suchlicki Over a year ago

@DrGriff You are correct, I didn't consider that. Though the main point I was trying to make is that < X /> and < X>< /X> are identical in meaning, per the XML spec. So you would need to actually exclude the element (when you construct the XML in your C# code) to signify NULL vs. empty. Anyway, I edited the answer to offer one possible solution based on EXIST method. Hope it helps.

roman Over a year ago

don't know why this one was downvoted, here's my upvote :) It's not very elegant way to do this, but it should work

Collectives™ on Stack Overflow

SQL Server read xml values as empty strings

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related