June 9, 2014 at 4:31 am
Hi All,
I m facing a prblm in generating the XML. Please help me guys.
My Current Output :
<Root>
<FieldName FieldName="TierName" Visibility="0" Mask="1" MaskLength="3" ReadOnly="0" />
<FieldName FieldName="TierCode" Visibility="1" Mask="0" MaskLength="0" ReadOnly="0" />
<FieldName FieldName="HierarchyCode" Visibility="0" Mask="1" MaskLength="0" ReadOnly="0" />
</Root>
Expected Ouput :
<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>
I had given the test script below,
Create Table Test
(
Id BigInt Identity(1,1),
RoleId BigInt,
FieldName Varchar(50),
isVisible Bit, -- 0 visible 1 - hidden
isMask Bit, -- 0 No Mask 1 - Masked
MaskLength int Default 0,
isReadOnly Bit, -- 0 Editable 1 - ReadOnly,
isMultiSelect Bit --0 Single 1 Multi
)
Insert into Test
Select 18566, 'TierName', 0, 1, 3, 0, 0
Union All
Select 18566, 'TierCode', 1, 0, 0, 0, 0
Union All
Select 18566, 'HierarchyCode', 0, 1, 0, 0, 0
And the query which i tried is,
Select FieldName as '@FieldName',
isVisible as '@Visibility', isMask as '@Mask', MaskLength as '@MaskLength', isReadOnly as '@ReadOnly' From
(
Select * From Test
)A
FOR XML PATH('FieldName'), Root('Root')
June 9, 2014 at 10:40 pm
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>
June 9, 2014 at 11:01 pm
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 🙁 🙁
June 9, 2014 at 11:11 pm
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.
😎
June 10, 2014 at 6:53 pm
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.
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
June 11, 2014 at 12:18 am
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.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply