XML to SQL Table
-
Can someone guide me how to get a file as below into a sql table - where I will be able to work with it a little better?
Thanks
(i have multiple files)
<?xml version='1.0' encoding='UTF-8' standalone='yes' ?>
<!--File Created By SMS Backup & Restore v8.20.27 on 03/06/2017 17:19:03-->
<?xml-stylesheet type="text/xsl" href="sms.xsl"?>
<smses count="3" backup_set="1ce73280-c28d-4a4e-b103-6929a9dc70d4" backup_date="1496506743393">
<sms protocol="0" address="+447969407900" date="1488446511943" type="1" subject="null" body="From Vodafone: You missed 1 call(s) from +447969407900 on 2 Mar 09:20 To deactivate this service dial 121 and choose 'Mailbox Settings'" toa="null" sc_toa="null" service_center="+447785016005" read="1" status="-1" locked="0" date_sent="1488446436000" readable_date="2 Mar 2017 09:21:51" contact_name="Me" />
<sms protocol="0" address="07969407900" date="1496506709042" type="2" subject="null" body="How's it going?" toa="null" sc_toa="null" service_center="null" read="1" status="-1" locked="0" date_sent="0" readable_date="3 Jun 2017 17:18:29" contact_name="Me" />
<sms protocol="0" address="+447969407900" date="1496506710326" type="1" subject="null" body="How's it going?" toa="null" sc_toa="null" service_center="+447785016005" read="0" status="-1" locked="0" date_sent="1496506712000" readable_date="3 Jun 2017 17:18:30" contact_name="Me" />
</smses> - Adam Sandler - Saturday, June 3, 2017 10:23 AM
What elements and attributes to you need from the XML, which data types do you use for each of those and do you have an XSD for the XML data?
- Eirikur Eiriksson - Saturday, June 3, 2017 10:31 AMAdam Sandler - Saturday, June 3, 2017 10:23 AM
What elements and attributes to you need from the XML, which data types do you use for each of those and do you have an XSD for the XML data?
I want to get into a SQL table as much of the data as possible - so as many sms element attributes as possible, in the most appropriate data type, which I guess I will determine when I move it over. I have xsl files no xsd.
Thanks
-
DECLARE @XML XML;SET @XML = '<?xml version=''1.0'' encoding=''UTF-8'' standalone=''yes'' ?>
<!--File Created By SMS Backup & Restore v8.20.27 on 03/06/2017 17:19:03-->
<?xml-stylesheet type="text/xsl" href="sms.xsl"?>
<smses count="3" backup_set="1ce73280-c28d-4a4e-b103-6929a9dc70d4" backup_date="1496506743393">
<sms protocol="0" address="+447969407900" date="1488446511943" type="1" subject="null" body="From Vodafone: You missed 1 call(s) from +447969407900 on 2 Mar 09:20 To deactivate this service dial 121 and choose ''Mailbox Settings''" toa="null" sc_toa="null" service_center="+447785016005" read="1" status="-1" locked="0" date_sent="1488446436000" readable_date="2 Mar 2017 09:21:51" contact_name="Me" />
<sms protocol="0" address="07969407900" date="1496506709042" type="2" subject="null" body="How''s it going?" toa="null" sc_toa="null" service_center="null" read="1" status="-1" locked="0" date_sent="0" readable_date="3 Jun 2017 17:18:29" contact_name="Me" />
<sms protocol="0" address="+447969407900" date="1496506710326" type="1" subject="null" body="How''s it going?" toa="null" sc_toa="null" service_center="+447785016005" read="0" status="-1" locked="0" date_sent="1496506712000" readable_date="3 Jun 2017 17:18:30" contact_name="Me" />
</smses>';
--INSERT INTO dbo.<TABLENAME>
SELECT
b.value('@protocol', 'int') as protocol,
b.value('@address', 'varchar(50)') as [address],
b.value('@date', 'varchar(50)') as [date],
b.value('@type', 'varchar(50)') as [type],
b.value('@subject', 'varchar(50)') as [subject],
b.value('@body', 'varchar(50)') as body,
b.value('@toa', 'varchar(50)') as toa,
b.value('@sc_toa', 'varchar(50)') as sc_toa,
b.value('@service_center', 'varchar(50)') as service_center,
b.value('@read', 'varchar(50)') as [read],
b.value('@status', 'varchar(50)') as [status],
b.value('@locked', 'varchar(50)') as locked,
b.value('@date_sent', 'varchar(50)') as date_sent,
b.value('@readable_date', 'varchar(50)') as readable_date,
b.value('@contact_name', 'varchar(50)') as contact_name
FROM @XML.nodes('/smses/sms') as a(b)Don't forget to change datatypes to suit the expected data.
I would use SSIS to manage and process the files
- gfoxxy93 - Friday, June 9, 2017 7:22 AM
Thank you very much. Just what I was after. How would you loop thorugh the files in SSIS. Obviously a for each for container the xml files. Then how would you recommend setting the @XML for each loop? Thanks. Much appreciated.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply