How read xml data from a xml file without tags in SQL Server?

Question

I know how to read the xml data from a file where the info is organized in tags, I mean a file like this:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <Administrador>
        <id> 8 </id>
        <nombre> Nelle </nombre>
        <valorDocId> 8399335355 </valorDocId>
        <contrasenna> Glenn </contrasenna>
    </Administrador>
    <Administrador>
        <id> 9 </id>
        <nombre> Gayler </nombre>
        <valorDocId> 1310348693 </valorDocId>
        <contrasenna> Madonna </contrasenna>
    </Administrador>
</dataset>

The code I used to read it is:

use Proyecto1

declare @filedata XML

select @filedata=BulkColumn from OpenRowSet(Bulk'File directory', Single_blob) x;
insert into Table(id, nombre, valorDocId, clave)
select 
    xData.value('id[1]', 'int') id,
    xData.value('nombre[1]','varchar(30)') nombre,
    xData.value('valorDocId[1]','int') valorDocId,
    xData.value('contrasenna[1]','varchar(20)') clave
from @fileData.nodes('/dataset/Administrador') as 
x(xData)

But now I need to read a xml file that is not organized in tags, at least not like the last one, the xml is like this:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <Administrador id="1" nombre="Nelle" valorDocId="8399335355" contrasenna="Glenn"/>
    <Administrador id="2" nombre="Gayler" valorDocId="1310348693" contrasenna="Madonna"/>
</dataset>

But the code I used before doesn't works, it throws an error that says that I can't insert a NULL value in the column 'id', so what I supposed is that the data is not being read. So how can I read that second file?

John Cappelletti · Accepted Answer · 2018-09-01 22:12:28Z

1

Example

Declare @XML xml = '
<dataset>
    <Administrador id="1" nombre="Nelle" valorDocId="8399335355" contrasenna="Glenn"/>
    <Administrador id="2" nombre="Gayler" valorDocId="1310348693" contrasenna="Madonna"/>
</dataset>
'

Select id          = x.v.value('@id','int')
      ,nombre      = x.v.value('@nombre','varchar(50)')
      ,valorDocId  = x.v.value('@valorDocId','varchar(50)')
      ,contrasenna = x.v.value('@contrasenna','varchar(50)')
 From  @Xml.nodes('dataset/Administrador') x(v)

Returns

id  nombre  valorDocId  contrasenna
1   Nelle   8399335355  Glenn
2   Gayler  1310348693  Madonna

EDIT - To Get your XML from a File

Declare @XML xml
Select @XML = BulkColumn FROM  OPENROWSET(BULK 'C:\Working\SomeXMLFile.xml', SINGLE_BLOB) x;

Select id          = x.v.value('@id','int')
      ,nombre      = x.v.value('@nombre','varchar(50)')
      ,valorDocId  = x.v.value('@valorDocId','varchar(50)')
      ,contrasenna = x.v.value('@contrasenna','varchar(50)')
 From  @Xml.nodes('dataset/Administrador') x(v)

edited Sep 1, 2018 at 22:12

answered Sep 1, 2018 at 21:34

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Fabricio Ceciliano Over a year ago

There isnt' a way to read all the info without coping the xml content in a variable, just like the other way? I have a 4k lines xml file and doing that would not be correct. By the way, I need to insert the read data in a table called Administrador

Fabricio Ceciliano Over a year ago

My XML is in a file and I need to insert it's data in a table

Fabricio Ceciliano Over a year ago

Just missing the insert into Table to insert it into the table I need. Thank you

John Cappelletti Over a year ago

@FabricioCeciliano Happy to help

Collectives™ on Stack Overflow

How read xml data from a xml file without tags in SQL Server?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related