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