Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to filter the namespace from the xml in SQLServer..plz help Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 12:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80
i have fetched a big xml from db.there is one namespace attached with it.i want to remove the namespace.plz help
Post #1360396
Posted Tuesday, September 18, 2012 9:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,606, Visits: 1,640
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.

Post #1360853
Posted Wednesday, September 19, 2012 1:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80
Actually what i am doing is my query is fetching xml from db which contains the name space
like
<XML xmmlns:https://temp.../test.xsd>

Declare @xml xml

select @xml =xmlcontent from tablename where id =4
this @xml looks like
<XML xmmlns:https://temp.../test.xsd>
<>
<>
.....


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
Post #1361177
Posted Wednesday, September 19, 2012 3:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,606, Visits: 1,640
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')

Post #1361217
Posted Wednesday, September 19, 2012 7:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80
actually i need to get the output of filtered xml as


<XML >
<>
<>
.....


plz suggest a way


Post #1361317
Posted Wednesday, September 19, 2012 12:23 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 AM
Points: 2,606, Visits: 1,640
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....
Post #1361556
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse