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 a tabular form

providerID Address city

1 address1 NULL

2 address2 NULL

3 address3 kol

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)

The output of this is

SN NodeName NodeValue

1Addressaddress1

1Addressaddress2

1Addressaddress3

2city kol

3providerID1

3providerID3

3providerID2

I just need to re arrange this record set to

providerID Address city

1 address1 NULL

2 address2 NULL

3 address3 kol

plz help!!