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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply