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

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?

1 Answer
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:WorkingSomeXMLFile.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)


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.