how to remove namespace from an existing xml subtr

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to remove namespace from an existing xml subtr

Author

Topic

patient0
Starting Member

2 Posts

Posted - 2014-06-20 : 01:49:49

I'm facing an issue with removing namespace from existing xml subtree.

For Example: I have a table with 1 field - data type xmltype. One entry is holding this xml:

<Item xmlns="http://www.w3.org/2001/XMLSchema-instance">
<Box>Blaff</Box>
<Door>Steal</Door>
<Chair>Wood</Chair>
</Item>
I would like to remove the namespace - i.e.:

<Item>
<Box>Black-box</Box>
<Door>Steal</Door>
<Chair>Wood</Chair>
</Item>
Do you have any suggestions? - I tried using the UPDATE() function with no luck.
Thank you very much for the help.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-06-20 : 02:17:32

just see if this works for you
UPDATE myTable
SET myColumn = REPLACE(myColumn,'xmlns="http://www.w3.org/2001/XMLSchema-instance"', '')

Javeed Ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-20 : 03:19:21

Why is it important to remove the namespace?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-20 : 03:46:12

[code]DECLARE @Data XML = ' <Item xmlns="http://www.w3.org/2001/XMLSchema-instance">
<Box>Blaff</Box>
<Door>Steal</Door>
<Chair>Wood</Chair>
</Item>';

-- No result
SELECT r.n.value('(Box[1])', 'VARCHAR(100)') AS [Box],
r.n.value('(Door[1])', 'VARCHAR(100)') AS [Door],
r.n.value('(Chair[1])', 'VARCHAR(100)') AS [Chair]
FROM @Data.nodes('(/Item)') AS r(n);

-- With result
WITH XMLNAMESPACES(DEFAULT 'http://www.w3.org/2001/XMLSchema-instance')
SELECT r.n.value('(Box[1])', 'VARCHAR(100)') AS [Box],
r.n.value('(Door[1])', 'VARCHAR(100)') AS [Door],
r.n.value('(Chair[1])', 'VARCHAR(100)') AS [Chair]
FROM @Data.nodes('(/Item)') AS r(n);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

patient0
Starting Member

2 Posts

Posted - 2014-06-22 : 13:30:47

Thank you! Replace() did the job.

quote:
Originally posted by ahmeds08

just see if this works for you
UPDATE myTable
SET myColumn = REPLACE(myColumn,'xmlns="http://www.w3.org/2001/XMLSchema-instance"', '')

Javeed Ahmed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-06-22 : 13:43:45

I hope you realize all complications regarding removing the namespace.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources