How to modify what "FOR XML PATH" returns

Question

Post reply

How to modify what "FOR XML PATH" returns

Shawn Therrien

Ten Centuries

Points: 1193
More actions
June 11, 2009 at 1:00 pm

#134990

DECLARE @Scores AS TABLE (ID VARCHAR(3), Score VARCHAR(3))
INSERT INTO @Scores
SELECT '15', '95' UNION
SELECT '8', '55' UNION
SELECT '14', ''
SELECT * FROM @Scores FOR XML PATH ('Record')
An example of what this returns:
14

15
95
8
55
So here is my questions about how I modify this.
1. How to I properly return a "null" answer? It should be:
-- to indicate that I am purposefully indicating null and not just leaving it out. Bbut if I have a NULL in my table, it's just excluded and I'm required to have this element even in the case of a null. I have to use ISNULL(Score,'') to get an element to show up, but that shows up like an opening/closing tag, not an empty tag.
2. How do I append XML to this for proper XML formatting?
It should look something like so:
14

15
95
8
55
The only thought I have is to throw this into a varchar and use string manipulation but I'm sure we're going to run into size issues as if you have thousands of records you can blow through a VARCHAR(8000) with XML fairly easily.
Would I assign this to the XML Data Type and modify this from there?
Thank you for any help you can give!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

I don't understand your first question.

For your second (if your attribute is not mandatory)

DECLARE @Scores AS TABLE (ID VARCHAR(3), Score VARCHAR(3))

INSERT INTO @Scores

SELECT '15', '95' UNION

SELECT '8', '55' UNION

SELECT '14', ''

SELECT

*

FROM @Scores

FOR XML PATH ('Record'), ROOT('testscore')

If your attribute is required the only way might be a sub-query with RAW.

Flo

LutzM SSC Guru Points: 107049 More actions · Answer 2

If I understand you correctly your data(=elements) "disappear" from the xml structure if the value is null.

There are two options I know of how to deal with it:

1) You can modify your SELECT clause to replace NULL values by using ISNULL(COLUMN, replacement value). This will make sure all elements will be displayed.

2) You could add ", ELEMENTS XSINIL" after the FOR XML clause. This will force a value of xsi:nil="true" as an replacement of NULL values.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Shawn Therrien Ten Centuries Points: 1193 More actions · Answer 3

I'm sorry, the forums didn't display the example I wrote and then now they're transforming it to a an empty value rather than a null value.

The service that must consume this XML requires an explicit tag to show null:

(open angle bracket)(fieldname)(space)(forward slash)(close angle bracket)

Short of taking a picture and hosting it, I'm not sure how else to explain it. It's basic HTML/XML I learned years ago and is in use in an XML consumer that hundreds of users are using.

Having an opening/closing tag will indicate that I want a blank value ('') in there, not a NULL.

--

So that out of the way, yes I know that this won't return the element if it's null and that won't work for me. I need a single NULL tag. Right now I'm passing a blank string and it's giving me the open/closing tags and I'm not sure if the consumer can read this properly or if it's going to cause problems.

Are there any XML functions you can use for this? Maybe I need to use another method to generate the XML?

Shawn Therrien Ten Centuries Points: 1193 More actions · Answer 4

Florian Reischl (6/11/2009)
For your second (if your attribute is not mandatory)
DECLARE @Scores AS TABLE (ID VARCHAR(3), Score VARCHAR(3))
INSERT INTO @Scores
SELECT '15', '95' UNION
SELECT '8', '55' UNION
SELECT '14', ''
SELECT
*
FROM @Scores
FOR XML PATH ('Record'), ROOT('testscore')
If your attribute is required the only way might be a sub-query with RAW.
Flo

Ok, this is good. A parameter for FOR XML PATH. I think my SQL Server Help isn't working (locally), when I looked up FOR XML I got a vague page about SQL Server and nothing about this function.

Yes, the element is required. What is RAW? -- Nevermind. I found it in the FOR XML documentation! thanks a lot!

Shawn Therrien Ten Centuries Points: 1193 More actions · Answer 5

lmu92 (6/11/2009)
If I understand you correctly your data(=elements) "disappear" from the xml structure if the value is null.
There are two options I know of how to deal with it:
1) You can modify your SELECT clause to replace NULL values by using ISNULL(COLUMN, replacement value). This will make sure all elements will be displayed.

Yes, I'm using ISNULL(Column,'') which gives you an opening/closing tag. I believe this will be interpreted as a blank space (or empty string) as the fields are all text and not a NULL value which is what it is.

It would be far easier if they interpreted a field not there as being a null... well easier for me I suppose. 🙂

lmu92 (6/11/2009)
2) You could add ", ELEMENTS XSINIL" after the FOR XML clause. This will force a value of xsi:nil="true" as an replacement of NULL values.

Yes, this just might work! It adds an extra element in there to define that it's null, but unless they're doing exact string parsing, their XML tools should be able to read that in fine. I'm going to test that out

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 6

Shawn Therrien (6/11/2009)
Yes, the element is required. What is RAW? -- Nevermind. I found it in the FOR XML documentation! thanks a lot!

RAW returns data as attributes. You can use it in combination with a sub-query to format your XML:

SELECT

'MCAD' type,

(

SELECT

*

FROM @Scores

FOR XML PATH('record'), TYPE

)

FOR XML RAW('testscore')

Flo