T-SQL converting XML string to table data

Question

I'm new to transforming XML strings into SQL Server tables. When I execute the following code, it produces tabular data in the result window of Management Studio. If I try to uncomment the insert into line the the query errors out. The table I've created looks like this:

And the T-SQL is this (I'm uncertain what I'm doing wrong):

DECLARE @XML XML

SET @XML = '
    <Codes>
      <Code>
        <TranCode>09764812</TranCode>
        <TranDescr>WP FBCCP ADJUSTMENT</TranDescr>
        <PosTranCode>77724</PosTranCode>
        <NegTranCode>88820</NegTranCode>
      </Code>
      <Code>
        <TranCode>09764804</TranCode>
        <TranDescr>WP FBCCP CRM</TranDescr>
        <PosTranCode>77724</PosTranCode>
        <NegTranCode>88820</NegTranCode>
      </Code>
      <Code>
        <TranCode>09764804</TranCode>
        <TranDescr>WP FBCCP CRM</TranDescr>
        <PosTranCode>77724</PosTranCode>
        <NegTranCode>88820</NegTranCode>
      </Code>
    </Codes>'

DECLARE @handle INT  
DECLARE @PrepareXmlStatus INT  

EXEC @PrepareXmlStatus= sp_xml_preparedocument @handle OUTPUT, @XML  

--INSERT INTO 670_TransCodes (TranCode,TranDescr,PosTranCode,NegTranCode)
    SELECT TranCode,TranDescr,PosTranCode,NegTranCode
    FROM OPENXML(@handle, '/Codes/Code', 2)  
        WITH (
        TranCode varchar(20) 'TranCode',
        TranDescr varchar(50) 'TranDescr',
        PosTranCode INT 'PosTranCode',
        NegTranCode INT 'NegTranCode'
        )

    EXEC sp_xml_removedocument @handle

I'm unable to reproduce this using your query with a temp table. Are you sure 670_TransCodes column names are correct in your query? — Jacob H
– Jacob H, Commented May 2, 2018 at 14:22

Yogesh Sharma · Accepted Answer · 2018-05-02 14:26:11Z

4

You need to surround table name by []

INSERT INTO [670_TransCodes] (TranCode,TranDescr,PosTranCode,NegTranCode)

I would use xml node method instead

INSERT INTO [670_TransCodes] (TranCode,TranDescr,PosTranCode,NegTranCode)
select a.value('TranCode[1]', 'varchar(20)') as TranCode,
       a.value('TranDescr[1]', 'varchar(50)') as TranDescr,
       a.value('PosTranCode[1]', 'INT') as TranDescr,
       a.value('NegTranCode[1]', 'INT')  as NegTranCode    
from @XML.nodes('/Codes/Code') as t(a);

edited May 2, 2018 at 14:26

answered May 2, 2018 at 14:19

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ѺȐeallү Over a year ago

The issue was definitely with my non standard table name...thank you for the suggested method. :)

Collectives™ on Stack Overflow

T-SQL converting XML string to table data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related