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 915and 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!!!