January 5, 2022 at 4:22 am
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.
January 5, 2022 at 9:58 am
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);
January 5, 2022 at 10:52 am
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);
January 5, 2022 at 11:01 am
Thanks Des, that is much tidier.
January 5, 2022 at 10:39 pm
Thank you guys, that worked.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy