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 =

'<XML>

<Provider>

<providerID>1</providerID>

<Address>address1</Address>

</Provider>

<Provider>

<providerID>2</providerID>

<Address>address2</Address>

</Provider>

<Provider>

<providerID>3</providerID>

<Address>address3</Address>

<city>kol</city>

</Provider>

</XML>'

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)

its out put is

1Addressaddress1

1Addressaddress2

1Addressaddress3

2city kol

3providerID 1

3providerID 3

3providerID 2

now i have to just customize it to

1address1NULL

2address2NULL

3address3 kol

please help me to achieve this thing..atleast if not fully generalised