Problem with generating XML in MS SQL

Question

Post reply

Problem with generating XML in MS SQL

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

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

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 1

You need a little bit of trickery here.

😎

USE tempdb;

GO

SELECT

(

SELECT

CAST('<' + TX.FieldName

+ ' Visibility="' + CAST(TX.isVisible AS VARCHAR(12))

+ '" Mask="' + CAST(TX.isMask AS VARCHAR(12))

+ '" MaskLength="' + CAST(TX.MaskLength AS VARCHAR(12))

+ '" ReadOnly="' + CAST(TX.isReadOnly AS VARCHAR(12))

+ '" />' AS XML)

FROM dbo.Test TX

FOR XML PATH(''),TYPE)

FOR XML PATH('Root')

Results

<Root>

<TierName Visibility="0" Mask="1" MaskLength="3" ReadOnly="0" />

<TierCode Visibility="1" Mask="0" MaskLength="0" ReadOnly="0" />

<HierarchyCode Visibility="0" Mask="1" MaskLength="0" ReadOnly="0" />

</Root>

subbubally Grasshopper Points: 20 More actions · Answer 2

Hi Eirikur,

Thanks for your reply, i already tried this solution too, if we go by concatenation, there may be performance in future, that's the reason, i avoided the concatenation logic 🙁 🙁

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 3

subbubally (6/9/2014)
Hi Eirikur,
Thanks for your reply, i already tried this solution too, if we go by concatenation, there may be performance in future, that's the reason, i avoided the concatenation logic 🙁 🙁

It is a question of choosing the lesser evil, other options are crosstab or pivot, more complex, harder to maintain and less scalable.

😎

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

Sub,

What performance concerns are you worried about from the concatonation technique? You should see little to no performance difference for a concatonation technique vs. a more standard FOR XML technique. I'm curious what you've witnessed to make you concerned, I only have up to SQL 2k8 available to me so I'm wondering if things have changed.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Eirikur Eiriksson SSC Guru Points: 182951 More actions · Answer 5

Evil Kraig F (6/10/2014)
Sub,
What performance concerns are you worried about from the concatonation technique? You should see little to no performance difference for a concatonation technique vs. a more standard FOR XML technique. I'm curious what you've witnessed to make you concerned, I only have up to SQL 2k8 available to me so I'm wondering if things have changed.

In my experience, only for the better.

😎