How to filter the namespace from the xml in SQLServer..plz help

Question

How to filter the namespace from the xml in SQLServer..plz help

suvo.kundu

SSC Eights!

Points: 953
More actions
September 17, 2012 at 12:32 pm

#100855

i have fetched a big xml from db.there is one namespace attached with it.i want to remove the namespace.plz help

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 1

AFAIK you can't remove the namespace without doing some kind hack like casting the XML to a varchar(max) and then replacing out the namespace and any prefixes that it uses before casting back to xml again. This isn't a nice thing to do and prone to problems for obvious reasons. You could use a xslt to do this though and would be more reliable than using REPLACE() within TSQL.

Working with namespaces is pretty easy though and all you need is to have something like this before your query:

WITH XMLNAMESPACES(DEFAULT 'http://somenamespace')

If you want to post an example xml document showing what your namespace looks like, we can help further with the syntax so that you can query the xml without removing out the namespace.

suvo.kundu SSC Eights! Points: 953 More actions · Answer 2

Actually what i am doing is my query is fetching xml from db which contains the name space

like

Declare @xml xml

select @xml =xmlcontent from tablename where id =4

this @xml looks like

<>

.....

i want to remove the namespace 'xmmlns:https://temp.../test.xsd'

plz suggest me the query how to use

'WITH XMLNAMESPACES(DEFAULT 'http://somenamespace')'

in this sql

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 3

Here you go, here are two example queries querying an xml structure that has a namespace. The first query will actually include some auto generated prefixes by SQL, but the second query doesn't:

DECLARE @xml XML

SET @xml = '

<XML xmlns="https://temp.../test.xsd">

<Content>

<Data>

<Node1>1</Node1>

<Node2>2</Node2>

</Data>

<Data>

<Node1>1</Node1>

<Node2>2</Node2>

</Data>

</Content>

</XML>'

--using WITH XMLNAMESPACES

;WITH XMLNAMESPACES(DEFAULT 'https://temp.../test.xsd')

SELECT @xml.query('//Content')

--OR to return xml without the p1 prefixes:

SELECT @xml.query(

'declare default element namespace "https://temp.../test.xsd";

//Content')

suvo.kundu SSC Eights! Points: 953 More actions · Answer 4

actually i need to get the output of filtered xml as

<XML >

<>

.....

plz suggest a way

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 5

As I mentioned previously, AFAIK, there is no real way to just strip out a namespace from an xml structure by using TSQL other than to cast the xml to a varchar(max) and hack it out by using REPLACE(). But as shown, working with xml namespaces is relatively straight forward....