how can i insert xml value into SQL table

stellusm...

SOLVED CLOSED ACTIVE ANSWERED
User: stellusmca
Posted: on Jun 15, 2016 05:06 AM
Forum: SQL Server
Answer: 1
Views: 4923

Hi all,

I have some clarification for the below sql query.

my xml is :

<main>
    <Data>
        <COUNTER>A</COUNTER> 
    </Data>
</main>

my query is :

CREATE TABLE #temp
(
   
    COUNTER VARCHAR(1)
);
 
INSERT #temp
SELECT    
    Data.value('(/COUNTER/text())[1]','VARCHAR(100)') AS COUNTER,

FROM
    @XML.nodes('/main/Data') AS AdviceData (Data);

INSERT INTO SECONDTABLE(COUNTER)
SELECT CASE T.COUNTER WHEN 'A' THEN 1 END AS COUNTER
FROM #temp T

The above query is working fine. but i don't want to use case statement. why because sometimes counter is Z then i should show 26. without case statement how can i achieve this.

thanks in advance

Download FREE API for Word, Excel and PDF in ASP.Net

Shashika...

0 4

Reply
ANSWER
User: Shashikant
Replied: on Jun 15, 2016 06:26 AM
Report

Please refer below link

CREATE TABLE #TABLENAME(OrderDate DATETIME,COUNTER VARCHAR(10));
  
DECLARE @XML AS XML
SET @XML = '<main>
    <Data>
        <COUNTER>A</COUNTER>
    </Data>
	  <Data>
        <COUNTER>B</COUNTER>
    </Data>
</main>';

SELECT   
    Data.value('(/COUNTER/text())[1]','VARCHAR(100)') AS COUNTER
INTO #Temp
FROM
   @XML.nodes('/main/Data') AS AdviceData (Data);

INSERT #TABLENAME(OrderDate,COUNTER)                            
SELECT CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),112)) AS  OrderDate,        
	(ASCII(CH.COUNTER) - 64) AS COUNTER
FROM #TEMP CH(NOLOCK)    

SELECT * FROM #temp
SELECT * FROM #TABLENAME

DROP TABLE #Temp
DROP TABLE #TABLENAME

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

how can i insert xml value into SQL table

Company

Explore

Follow us

Contact Us