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

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

I think you may need to provide a little more information on what you are trying to do, sample code and what issues/errors that you are seeing. Showing an example...

I got the answer right but got the intention of the question wrong.

When I thought, can I get that script to run without changing it, my method to achieve this...

Nice.... I like that solution 🙂

This means that the argument that you are specifying within the nodes() function must be a string literal...

Really good question. thanks

ok, without seeing more of the actual xml it is hard for me to comment on that.

However, have you seen this thread:

http://www.sqlservercentral.com/Forums/Topic1311855-21-1.aspx#bm1312389

I came up with this for someone who had...

Did you have a look at my previous reply regarding the function developed by jacob sebastian? That will shred an xml structure to a flat table similar to what you...

Try this revised query:

Declare @xml xml

Set @xml =

'<XML>

<Provider>

<providerID>1</providerID>

<Address>address1</Address>

</Provider>

<Provider>

<providerID>2</providerID>

<Address>address2</Address>

</Provider>...

I would use an XQuery flwor expression in your scenario. Here is an example to remodel the xml that you have provided and add the extra nodes to the xml...

I sometimes use this function if I find myself in a similar situation where I need to shred an xml structure to a tabular form but do not know the...

Great question!... i kept counting 10 records until I realised that "Doug" would actually be eliminated from the apply.

Thanks.

Nice question, although i think the answers made it easier just through a process of elimination. 🙂

Good question. thanks.