Home Forums Programming XML how to get the node names as well as node values from xml in sqlserver RE: how to get the node names as well as node values from xml in sqlserver

  • 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