ya i could get my solution as

select

ZZ.gid,

ZZ.Record1 as 'OldRecord1',

CASE WHEN XX.Record1 IS NULL THEN 'No New Record' ELSE XX.Record1 END as 'NewRecord1' ,

ZZ.Record2 as 'OldRecord2',

CASE WHEN...

actually i need to get the output of filtered xml as

<XML >

<>

<>

.....

plz suggest a way

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

SELECT

dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) AS 'SN',

NodeName = C.value('local-name(.)', 'varchar(50)')

, NodeValue = C.value('(.)[1]', 'varchar(50)')

FROM @xml.nodes('/XML/Provider/*') AS T(C)

actually the xml that i am fetching is very large approx 5mb...

actually the out put of the query changes using ur suggestion.The value of SN column changes

set @starttag ='XML'

set @root ='Provider'

SELECT

dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) AS 'SN',

NodeName = C.value('local-name(.)',...

Actually i have a dynamic XML.I have only the starting root node of the xml.The child nodes are dynamic keeps on changing ...I have to translate to it into...

I am getting the out put of a query as

SN NodeName NodeValue

1 ...

i tried to achieve this by creating a table variable and altering its structure with the node name by traversing a loop. and again inserting the node values to the...

if i can manage to get the the starting and the root node as reference...i can make it very generalized like:-

Declare @xml xml

Set @xml = ...

see this is basically for statically getting the tabular structure where i know the node names.

but for the generalized work around whats the procedure can it be possible?

I will be...

The output of the query is:-

Declare @xml xml

Set @xml =

'<XML>

<Provider>

...

I could get the node names as column names

SELECT

distinct NodeName = C.value('local-name(.)', 'varchar(50)')

-- , NodeValue = C.value('(.)[1]',...