The output of the query is:-

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

distinct

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

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

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

output

Addressaddress1

Addressaddress2

Addressaddress3

citykol

providerID1

providerID2

providerID3

if any body can help me to change the above out put to this one it can still serve the purpose:-

ProvideId Address City

1 address1 NULL

2 address2 NULL

3 address3 kol

please help