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

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

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

  • 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

  • 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')

  • actually i need to get the output of filtered xml as

    <XML >

    <>

    <>

    .....

    plz suggest a way

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

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

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