June 9, 2014 at 4:33 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 used to generate the output,
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 6:56 am
You've not included what you've tried. How about this: -
SELECT FieldName AS 'FieldName/@FieldName',
isVisible AS 'FieldName/@Visibity',
isMask AS 'FieldName/@Mask',
MaskLength AS 'FieldName/@MaskLength',
isReadOnly AS 'FieldName/@ReadOnly'
FROM Test
FOR XML PATH(''), ROOT('Root');
Returns: -
<Root>
<FieldName FieldName="TierName" Visibity="0" Mask="1" MaskLength="3" ReadOnly="0" />
<FieldName FieldName="TierCode" Visibity="1" Mask="0" MaskLength="0" ReadOnly="0" />
<FieldName FieldName="HierarchyCode" Visibity="0" Mask="1" MaskLength="0" ReadOnly="0" />
</Root>
June 9, 2014 at 7:03 am
Hi,
Thanks for your reply. I wanted to have the FieldName ( value from the TEST table) as element name. I dont want this,
<FieldName FieldName="TierName" Visibity="0" Mask="1" MaskLength="3" ReadOnly="0" />
I wanted to get the output like this,
<TierName Visibity="0" Mask="1" MaskLength="3" ReadOnly="0" />
Where TierName is a value which is present in the TEST table.
June 10, 2014 at 3:16 am
subbubally (6/9/2014)
Hi,Thanks for your reply. I wanted to have the FieldName ( value from the TEST table) as element name. I dont want this,
<FieldName FieldName="TierName" Visibity="0" Mask="1" MaskLength="3" ReadOnly="0" />
I wanted to get the output like this,
<TierName Visibity="0" Mask="1" MaskLength="3" ReadOnly="0" />
Where TierName is a value which is present in the TEST table.
I see. In that case, the only way I can think of would be to use dynamic SQL: -
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = 'SELECT ' + STUFF((SELECT
',' + CAST(isVisible AS CHAR(1)) + ' AS ' + CHAR(39) + FieldName + '/@Visibity' + CHAR(39) + ',' + CHAR(13) + CHAR(10) +
CAST(isMask AS CHAR(1)) + ' AS ' + CHAR(39) + FieldName + '/@Mask' + CHAR(39) + ',' + CHAR(13) + CHAR(10) +
CAST(MaskLength AS VARCHAR(19)) + ' AS ' + CHAR(39) + FieldName + '/@MaskLength' + CHAR(39) + ',' + CHAR(13) + CHAR(10) +
CAST(isReadOnly AS CHAR(1)) + ' AS ' + CHAR(39) + FieldName + '/@ReadOnly' + CHAR(39) + CHAR(13) + CHAR(10)
FROM Test
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + 'FOR XML PATH('+CHAR(39)+CHAR(39)+'), ROOT('+CHAR(39)+'Root'+CHAR(39)+');';
EXECUTE sp_executesql @sql;
Which returns: -
<Root>
<TierName Visibity="0" Mask="1" MaskLength="3" ReadOnly="0" />
<TierCode Visibity="1" Mask="0" MaskLength="0" ReadOnly="0" />
<HierarchyCode Visibity="0" Mask="1" MaskLength="0" ReadOnly="0" />
</Root>
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply