Is there an easy way in SQL Server to take an XML Document and remove all
its values while leaving the structure, elements and attributes in place?
I have included a sample of what I would like below
For example take:
'<root>
<foo name="Joe"/>
<heading name="One">
<contents>some text</contents>
</heading>
</root>'
and return:
'<root>
<foo name=""/>
<heading name="">
<contents></contents>
</heading>
</root>'--This is a hack of a sorts, but it seems to work...
CREATE FUNCTION fnClearXmlValues (@.x xml)
RETURNS varchar(max)
AS
BEGIN
DECLARE @.t varchar(max)
SET @.t = ''
SELECT @.t = @.t + '<' + element + ' ' + attributes
+ CASE WHEN ChildCount > 0
THEN '>' + IsNull(dbo.fnClearXmlValues(Children), '')
+'</' + element + '>'
ELSE '/>'
END
FROM (SELECT
T.C.value('count(./*)', 'int') AS ChildCount,
T.C.query('./*') AS Children,
T.C.value('for $s in . return local-name($s)',
'varchar(255)') AS element,
CAST(T.C.query('for $s in ./@.* return
concat(local-name($s), "=""""")') as varchar(1000)) as attributes
FROM @.x.nodes('*') AS T(C)) AS x
RETURN @.t
END
--To use:
declare @.x xml
SET @.x = '<root>
<foo name="Joe" id="1"/>
<heading name="One">
<contents>some text</contents>
<otherContent> x
<test>y</test>
</otherContent>
</heading>
</root>'
SELECT CAST(dbo.fnClearXmlValues(@.x) AS xml)
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"Mark" <marksullivan@.rcn.com> wrote in message
news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Is there an easy way in SQL Server to take an XML Document and remove all
> its values while leaving the structure, elements and attributes in place?
> I have included a sample of what I would like below
> For example take:
> '<root>
> <foo name="Joe"/>
> <heading name="One">
> <contents>some text</contents>
> </heading>
> </root>'
> and return:
> '<root>
> <foo name=""/>
> <heading name="">
> <contents></contents>
> </heading>
> </root>'
>
>|||Hello
You can remove every text-nodes with modify(),
set @.x.modify('delete /descendant::text()')
However you can't apply the same xpath to the value of attributes. So some
loop may be needed.
while (@.x.exist('/descendant::*[@.* != ""]')=1)
BEGIN
set @.x.modify('
replace value of (//@.*[. != ""])[1] with ""
')
END
Alternatively, if the data is huge or you want neat solution, you may want
XSL via CLR. It's almost crime use SQL2005 and not use CLR. ^^;
"Mark" <marksullivan@.rcn.com> wrote in message
news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
> Is there an easy way in SQL Server to take an XML Document and remove all
> its values while leaving the structure, elements and attributes in place?
> I have included a sample of what I would like below
> For example take:
> '<root>
> <foo name="Joe"/>
> <heading name="One">
> <contents>some text</contents>
> </heading>
> </root>'
> and return:
> '<root>
> <foo name=""/>
> <heading name="">
> <contents></contents>
> </heading>
> </root>'
>
>|||One caveat to Han's solution is that @.x needs to be unconstrained by a
schema collection. Otherwise text() on a simple typed content element will
raise a type error.
Best regards
Michael
"Han" <hp4444@.kornet.net.korea> wrote in message
news:uIvMp49KHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hello
> You can remove every text-nodes with modify(),
> set @.x.modify('delete /descendant::text()')
> However you can't apply the same xpath to the value of attributes. So some
> loop may be needed.
> while (@.x.exist('/descendant::*[@.* != ""]')=1)
> BEGIN
> set @.x.modify('
> replace value of (//@.*[. != ""])[1] with ""
> ')
> END
> Alternatively, if the data is huge or you want neat solution, you may want
> XSL via CLR. It's almost crime use SQL2005 and not use CLR. ^^;
> "Mark" <marksullivan@.rcn.com> wrote in message
> news:OiIBTU1KHHA.4384@.TK2MSFTNGP03.phx.gbl...
>
