XML from rows (FOR XML RAW) with empty value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

XML from rows (FOR XML RAW) with empty value

Author

Topic

Scal
Starting Member

24 Posts

Posted - 2011-04-21 : 08:37:50

Hi;
I have a query which basically select all rows of a table and output it as an XML (in the form of HTML).

For some rows, I have empty value, and the XML generation creates a closed tag for those.

Example:
SET @Xml = (
SELECT
A.Command AS td,
'td' =
CASE
WHEN A.Argument1 IS NULL THEN ''
ELSE A.Argument1
END,
'td' =
CASE
WHEN A.Argument2 IS NULL THEN ''
ELSE A.Argument2
END
FROM
tbl1 AS A
INNER JOIN tbl2 AS B ON A.ID = B.A_ID
FOR XML RAW ('tr'), ELEMENTS, ROOT('tbody'), TYPE
)

Produces for empty column rows:
<td />

I need to produce "<td></td>", how can I force this? I tried to concatenate a empty space (ELSE A.Argument2 + '') but that produces "<td>#x20;</td>".

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 09:45:40

The behavior I have seen is that if the value is not null, it would would not use the short form for empty elements (or more precisely elements that have empty string data). For example, if you run this it uses short form.:


select null AS Argument1
UNION ALL
select '1' 
for xml RAW('tr'), root('tbody'),ELEMENTS

But the query below would not use the short form.


select '' AS Argument1
   UNION ALL
select '1' 
for xml RAW('tr'), root('tbody'),ELEMENTS

Since you are do not have nulls, I am unable to explain the behavior. Can you post the table DDL and some sample data that would show this behavior?

Scal
Starting Member

24 Posts

Posted - 2011-04-21 : 10:52:32

You are right sunitabeck, somehow while doing the table definition and some data to send here, I tried and it gives good result (no auto-closing tag).
The issue comes in when I use some variable to store and build the XML header/Footer.

I will post the SQL in a new post for clarity.

Thanks already for the help.

Scal
Starting Member

24 Posts

Posted - 2011-04-21 : 10:53:55

Table 1

CREATE TABLE [_tbl1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Command] [nvarchar](250) NOT NULL,
[Argument1] [nvarchar](1000) NULL,
[Argument2] [nvarchar](1000) NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [_tbl1]
([Command], [Argument1], [Argument2])
VALUES
('command 1', 'arg1 1', 'arg2 1')
GO
INSERT INTO [_tbl1]
([Command], [Argument1], [Argument2])
VALUES
('command 2', 'arg1 1', '')
GO

Table 2

CREATE TABLE [_tbl2](
[ID] [int] IDENTITY(1,1) NOT NULL,
[_tbl1_ID] [int] NOT NULL,
[_tbl3_ID] [int] NOT NULL,
CONSTRAINT [PK_ID2] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

INSERT INTO [_tbl2]
([_tbl1_ID], [_tbl3_ID])
VALUES
(1, 1)
GO
INSERT INTO [_tbl2]
([_tbl1_ID], [_tbl3_ID])
VALUES
(1, 2)
GO
INSERT INTO [_tbl2]
([_tbl1_ID], [_tbl3_ID])
VALUES
(2, 1)
GO
INSERT INTO [_tbl2]
([_tbl1_ID], [_tbl3_ID])
VALUES
(2, 2)
GO

Table 3

CREATE TABLE [_tbl3](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL
CONSTRAINT [PK_ID3] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [_tbl3]
([Name])
VALUES
('case 1')
GO
INSERT INTO [_tbl3]
([Name])
VALUES
('case 2')
GO

Scal
Starting Member

24 Posts

Posted - 2011-04-21 : 10:55:06

Query 1 (working)

SELECT
A.Command AS td,
CASE
WHEN A.Argument1 IS NULL THEN 'a'
WHEN A.Argument1 = '' THEN 'b'
ELSE A.Argument1
END AS td,
CASE
WHEN A.Argument2 IS NULL THEN 'c'
WHEN A.Argument2 = '' THEN ''
ELSE A.Argument2
END AS td
FROM
_tbl1 AS A
INNER JOIN _tbl2 AS B ON A.ID = B._tbl1_ID
INNER JOIN _tbl3 AS C ON B._tbl3_ID = C.ID
FOR XML RAW ('tr'), ELEMENTS, ROOT('tbody'), TYPE

Query 2 (raising the issue from original post)

DECLARE @Xml XML
DECLARE @XmlHeader NVARCHAR(1000)
DECLARE @XmlFooter NVARCHAR(100)

SET @XmlHeader = '<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head profile="http://selenium-ide.openqa.org/profiles/test-case">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="selenium.base" href="[ProjectBaseUrl]" />
<title>[TestCaseName]</title>
</head>
<body>
<table cellpadding="1" cellspacing="1" border="1">
<thead>
<tr><td rowspan="1" colspan="3">[TestCaseName]</td></tr>
</thead>'
SET @XmlFooter = '</table>
</body>
</html>'

SET @Xml = (
SELECT
A.Command AS td,
CASE
WHEN A.Argument1 IS NULL THEN 'a'
WHEN A.Argument1 = '' THEN 'b'
ELSE A.Argument1
END AS td,
CASE
WHEN A.Argument2 IS NULL THEN 'c'
WHEN A.Argument2 = '' THEN ''
ELSE A.Argument2
END AS td
FROM
_tbl1 AS A
INNER JOIN _tbl2 AS B ON A.ID = B._tbl1_ID
INNER JOIN _tbl3 AS C ON B._tbl3_ID = C.ID
FOR XML RAW ('tr'), ELEMENTS, ROOT('tbody'), TYPE
)
SELECT @XmlHeader + CAST(@Xml AS NVARCHAR(MAX)) + @XmlFooter

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 11:48:15

I see what you mean. When you cast XML to NVARCHAR(MAX), SQL helps us out by using auto-closing tag. I don't know of a way to force SQL to not do the auto-closing. This is consistent with what Microsoft says when the say that "It may not be an exact copy of the text XML". http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx

Only work around I can think of is to replace the empty string in your query with a space and then use CONVERT with option 1 or 3 (which forces preservation of white space) as in:


...
WHEN A.Argument2 = '' THEN ' ' -- a space character.
...

and then


CONVERT(NVARCHAR(MAX),@xml,1)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-21 : 13:26:46

would the REPLACE function help?
REPLACE(@Xml, '<td />','<td></td>')

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-21 : 13:56:13

quote:
Originally posted by yosiasz

would the REPLACE function help?
REPLACE(@Xml, '<td />','<td></td>')

If you don't have the passion to help people, you have no passion

Heh! Of course!! It just didn't occur to me that the final output is really a varchar and that the problem child is very well and precisely defined as being '<td />'

Thanks, yosiasz! We will wait for Scal to confirm.

Scal
Starting Member

24 Posts

Posted - 2011-04-22 : 03:15:34

When I do set a white space, WHEN A.Argument1 = '' THEN ' ', I get: <td>#x20;</td>

So I did use the REPLACE function.

By the way, if there is a better way of generating my XML, let me know :)
Thanks guys.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-22 : 08:17:37

If you use convert with format code 1 or 3, it should not cause the entitization like <td>#x20;</td>. For example the cast below will cause entitization, but the convert will not.

DECLARE @xml XML;
SELECT @xml = 
(
   SELECT Argument1 FROM 
   (
      SELECT ' ' Argument1 
      UNION ALL 
      SELECT '1'
   ) s
   for xml RAW('tr'), root('tbody'),ELEMENTS,TYPE 
);

SELECT CAST(@xml AS NVARCHAR(MAX)); -- entitizes to #x20;
SELECT CONVERT(NVARCHAR(MAX),@xml,1); -- does not entitize.

In any case, if you have your code working, it is only of academic interest, so you probably don't care

As for better methods for generating XML, as you probably know there are four choices: XML AUTO, XML RAW, XML PATH and XML EXPLICIT.

XML EXPLICT is deprecated, so I wouldn't use it for any new projects.

XML AUTO and XML RAW are quick and easy, but the XML can change if the table schema changes. Not just the element names, but even the shape can change.

That leaves XML PATH, which I think is the very best.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-22 : 14:08:24

sometimes the best solution is the simplest :) when you got your head dug in the code and you go way down the rabbit hole hard to pull out your head for simple solution ;) how about the elementxsinil directive. Would that help?

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-22 : 14:19:55

what version of SQL do you have and do you have the latest patches and SP of everything? Because I ran everything on my machine which is sql 2008 R2 and it works fine?

If you don't have the passion to help people, you have no passion

Scal
Starting Member

24 Posts

Posted - 2011-07-05 : 11:17:31

Hi yosiasz;
sorry for the late reply, been busy on plenty of things.

As far as I know the SQL server is up to date but I can't control that, only can relay on what the IT guy says. Anyway.

I ran into another problem. When I have a white space as value in a column, my CASE statement is not working correctly:

SET @Xml = (
SELECT
TA.Command AS td,
CASE
WHEN TA.Argument1 IS NULL THEN '-'
WHEN TA.Argument1 = '' THEN '-'
ELSE TA.Argument1
END AS td,
CASE
WHEN TA.Argument2 IS NULL THEN '-'
WHEN TA.Argument2 = '' THEN '-'
ELSE TA.Argument2
END AS td
FROM
TestActions AS TA
INNER JOIN TestCasesToTestActions AS TC2TA ON TA.ID = TC2TA.TestActions_ID
INNER JOIN TestCases AS TC ON TC2TA.TestCases_ID = TC.ID
WHERE
TC.ID = @TestCaseID
ORDER BY
TC2TA.OrderNumber ASC
FOR XML RAW ('tr'), ELEMENTS, ROOT('tbody'), TYPE
)

One of the record in TA.Argument2 is " " (white space), but for some reason, SQL returns it as "" (empty) although I don't "trim" the column value.
At first I thought it was because of the XML but if I simply call the SELECT statement, the column value is also "-", even when doing:

CASE
WHEN TA.Argument2 IS NULL THEN '-'
WHEN TA.Argument2 = ' ' THEN 'x'
WHEN TA.Argument2 = '' THEN 'y'
ELSE TA.Argument2
END AS td

Value becomes "x" no matter if the column value is "" or " ".
Why doesn't SQL keep data as they are?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources