Removing all values from an XML Document

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...
>