April 2, 2018 at 1:07 pm
Hello,
Hopefully I explain this right .
I have an utf-8 encoded XML file.
I am trying to "convert" into a traditional table rows and columns.
XML Data looks like this:
<?xml version="1.0" encoding="utf-8"?>
Easily have gotten data into SQL via bulk insert openrowset
INSERT INTO T1(XmlCol)
SELECT * FROM OPENROWSET(
BULK 'c:\drop\Device_Details.xml',
SINGLE_BLOB) AS x;
I'm able to query the data like this:
SELECT T2.Loc.query('.')
FROM T1
CROSS APPLY XmlCol.nodes('Data/Devices/Device') as T2(Loc)
After quite awhile with Google this is as close as I have gotten to getting rows and columns out of the data:
SELECT Rows.n.value('(@column2)[1]', 'varchar(20)'),
Rows.n.value('(@column2)[1]', 'nvarchar(100)'),
Rows.n.value('(@column3)[1]', 'int')
FROM T1
CROSS APPLY XmlCol.nodes('Data/Devices/Device') Rows(n)
However the above does not work because I think because it assumes data is more "traditional" xml format with start and end tags around each element instead of the utf-8 format I have
I made an XML data file that looks like this:
999-999-999 10749B MX 915
and then ran this and got the rows and columns I wanted.
SELECT Rows.n.value('(SN)[1]', 'varchar(20)') as Col1,
Rows.n.value('(DID)[1]', 'varchar(20)') as Col2,
Rows.n.value('(Model)[1]', 'varchar(20)') as Col3
FROM T3
CROSS APPLY XmlCol.nodes('Data/Devices/Device') Rows(n)
|Col1|Col2|Col3|
|999-999-999|10749B|MX 915|
How do I make this work with the data set I actually have?
Thanks!!!
April 3, 2018 at 5:14 am
You need to start with converting utf-8 to USC-2, which is readable for SQL Server.
Mr.Google can help you with several of such functions.
After that you may use XML parsing techniques.
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 2 (of 2 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