Inserting XML data into SQLServer returns only NULL values 1

[bmacbmac]

Hi there,

I have the following data in an xml file:

<?xml version="1.0" encoding="utf-16"?>
<documents>
  <document ArchivedFileName="40243702\6665414.tif" DocumentId="6665414" FileSize="304790" OriginalFileName="ptmcdfd.tif" DocumentTypeName="8" FileNumber="40243702" RecordingNumber="&lt;NULL&gt;" Description="re;inpsc" CreationDate="1/1/2020 11:07:34 AM" ModificationDate="1/1/2020 11:07:34 AM" VersionId="1" />
  <document ArchivedFileName="40243766\6665416.tif" DocumentId="6665416" FileSize="1011164" OriginalFileName="ptm4212.tif" DocumentTypeName="46" FileNumber="40243766" RecordingNumber="9211181468" Description="TDD" CreationDate="1/1/2020 11:12:57 AM" ModificationDate="1/1/2020 11:12:57 AM" VersionId="1" />
  <document ArchivedFileName="40243712\6665417.tif" DocumentId="6665417" FileSize="1175720" OriginalFileName="ptm1acd.tif" DocumentTypeName="46" FileNumber="40243712" RecordingNumber="641276" Description="EAS" CreationDate="1/1/2020 11:17:48 AM" ModificationDate="1/1/2020 11:17:48 AM" VersionId="1" />
  <document ArchivedFileName="40243722\6665418.pdf" DocumentId="6665418" FileSize="43214" OriginalFileName="exhibita-legaldescriptionwithpropertyaddress.pdf" DocumentTypeName="22" FileNumber="40243722" RecordingNumber="&lt;NULL&gt;" Description="ExhibitA-LegalDescriptionwithpropertyaddress" CreationDate="1/1/2020 11:25:04 AM" ModificationDate="1/1/2020 11:25:04 AM" VersionId="1" />
  <document ArchivedFileName="40243722\6665419.pdf" DocumentId="6665419" FileSize="295721" OriginalFileName="prelimcommitment.pdf" DocumentTypeName="5" FileNumber="40243722" RecordingNumber="&lt;NULL&gt;" Description="PrelimCommitment" CreationDate="1/1/2020 11:25:23 AM" ModificationDate="1/1/2020 11:25:23 AM" VersionId="1" />
<documents>

I'm trying to import this into an SQL table with the following query:

declare @X xml;
select @X = T.MY_XML
from openrowset(bulk 'd:\index2.xml', single_blob) as T(MY_XML)
select 
  MY_XML.document.value('(ArchivedFilename/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(DocumentID/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(Filesize/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(OriginalFileName/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(DocumentTypeName/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(FileNumber/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(RecordingNumber/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(Description/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(Creationdate/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(Modificationdate/text())[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(VersionID/text())[1]', 'VARCHAR(100)')
from @X.nodes('documents/document') AS MY_XML (document);

All it returns in NULL values.

Granted, I cheated and found this script to modify and import for my own project. It runs pretty fast returning over 200K rows in just a few seconds.

Can you see why I am getting NULL values instead of actual data? Is there a faster way to go?

Thanks!

 

[imex]

Hi,

Try:

select 
  MY_XML.document.value('(@ArchivedFileName)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@DocumentId)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@FileSize)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@OriginalFileName)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@DocumentTypeName)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@FileNumber)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@RecordingNumber)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@Description)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@CreationDate)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@ModificationDate)[1]', 'VARCHAR(100)'),
  MY_XML.document.value('(@VersionId)[1]', 'VARCHAR(100)')
from @X.nodes('/documents/document') AS MY_XML(document);

Hope this help

[URL unfurl="true"]https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[bmacbmac]

OMG you are a life saver! That worked perfectly!

 

[Andrzejek]

macbmac - Don't you think imex deserves a Star?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson