XML to SQL

Question

XML to SQL

PJ_SQL

SSCertifiable

Points: 6705
More actions
January 5, 2022 at 4:22 am

#3969975

hello!
I have table with "text" datatype column with this data below:
<SB><Case ID="123" Total="10.51" Num="1088"/>
<Case ID="567" Total="39.52" Num="345"/>
</SB>
How do I convert this to insert into another table with column name "Case ID" , "Total", "Num" with the corresponding values?

Thanks in advance.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247169 More actions · Answer 1

This gets you most of the way there. I have not yet worked out how to handle the space in 'Case ID' though.

DROP TABLE IF EXISTS #SomeXML;

CREATE TABLE #SomeXML
(
    TheData VARCHAR(MAX) NOT NULL
);

INSERT #SomeXML
(
    TheData
)
VALUES
('<SB><Case ID="123" Total="10.51" Num="1088"/>
<Case ID="567" Total="39.52" Num="345"/>
</SB>');

SELECT sx.TheData
      ,x.XMLData
      ,CaseId = p.n1.value('(@*:CaseID)[1]', 'INT')
      ,Total = p.n1.value('(@*:Total)[1]', 'decimal(19,2)')
      ,Num = p.n1.value('(@*:Num)[1]', 'INT')
FROM #SomeXML sx
    CROSS APPLY
(SELECT XMLData = CAST(sx.TheData AS XML)) x
    CROSS APPLY x.XMLData.nodes('SB/*') p(n1);

This reply was modified 3 years, 5 months ago by Phil Parkin.
This reply was modified 3 years, 5 months ago by Phil Parkin.

DesNorton SSC-Insane Points: 24517 More actions · Answer 2

Updating Phil's code, this will cater for the space in [Case ID]

CREATE TABLE #SomeXML ( TheData text NOT NULL );

INSERT  #SomeXML ( TheData )
VALUES ( '<SB><Case ID="123" Total="10.51" Num="1088"/>
<Case ID="567" Total="39.52" Num="345"/>
</SB>' );


--INSERT INTO YourSchema.YourNewTable ([Case ID], Total, Num)
SELECT      [Case ID] = p.n1.value( '@ID[1]', 'INT' )
          , Total     = p.n1.value( '@Total[1]', 'decimal(19,2)' )
          , Num       = p.n1.value( '@Num[1]', 'INT' )
--          , x.XMLData -- Uncomment for testing
FROM ( SELECT  XMLData = CAST(sx.TheData AS xml) FROM #SomeXML AS sx) AS x
CROSS APPLY x.XMLData.nodes( 'SB/Case' ) AS p(n1);