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 table variable..
i am finding some problem...in this
is this the right way to do so..plz advice
i have deined an udf like
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[udf_GetXMLNodeValue_Names](@xml xml)
returns table
as
--Declare @NodeName varchar(100)
return
(
--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)
)
my actual stored proc of getting the tabular structure is:-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter PROCEDURE [dbo].[spFetchNodeNames_ValuesFromXML]
AS
--begin try
--begin tran
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>'
declare @colName varchar(100);
declare @Selectsql varchar(100);
declare @sql varchar(100);
declare @count int;
declare @i int;
set @i=1;
set @sql='';
select @count=count(distinct NodeName) from dbo.udf_GetXMLNodeValue_Names (@xml)
--DECLARE @finaltable TABLE
--(
-- SN int identity
--
--
--)
IF EXISTS (SELECT * FROM sys.tables WHERE name LIKE '#temp%')
begin
DROP TABLE #temp
print ('hi')
end
SET @sql = 'CREATE TABLE temptab (SN int )'
EXEC (@sql)
WHILE (@i <=@count)
BEGIN
select @colName= NodeName from dbo.udf_GetXMLNodeValue_Names (@xml) where SN=@i;
select(@colName)
SET @sql = 'ALTER TABLE temptab ADD ' + @colName + ' VARCHAR(100)'
EXEC (@sql)
--INSERT INTO temptab VALUES ('COl 1')
SET @i = @i + 1
END
SET @sql = 'ALTER TABLE temptab DROP COLUMN SN'
EXEC (@sql)
select * from temptab
DROP TABLE temptab
select * from temptab
--commit
--end try
--begin catch
--rollback
--end catch