July 8, 2019 at 9:27 pm
Hello community,
I am trying to Importing and Processing data from XML files into SQL Server tables, following the example post on this site : https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/ by Arshad Ali. This is the first time i use this technique, then i put only one filed to display, but i dont know why the result is always empty.
I attach my XML file and post above the code:
CREATE TABLE XMLwithOpenXML
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)
INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'D:\Work\DOTT\saft_01-06-2019_30-06-2019.xml', SINGLE_BLOB) AS x;
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT InvoiceNo
FROM OPENXML(@hDoc, 'AuditFile/Header/MasterFiles/SourceDocuments/SalesInvoices/Invoice')
WITH
(
InvoiceNo [varchar](50) 'InvoiceNo'
)
EXEC sp_xml_removedocument @hDoc
GO
Many thanks for your help,
Best regards,
Luis
July 9, 2019 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
July 10, 2019 at 10:29 am
Hello Community,
I try using another technique to querying the XML File that i find in: https://www.red-gate.com/simple-talk/sql/database-administration/ad-hoc-xml-file-querying/ and post by Seth Delconte.
I use the Microsoft Xml file and i try the above code without problem, also i create new TAGS and everything Works.
WITH XmlFile (Contents) AS (
SELECT CONVERT (XML, BulkColumn)
FROM OPENROWSET (BULK 'D:\XML\DOTT\saft_01-05-2019_31-05-2019.xml', SINGLE_BLOB) AS XmlData
)
SELECT
c.value('(InvoiceNo)[1]', 'varchar(10)') AS [InvoiceNo]
--c.value('(Hash)[1]', 'varchar(200)') AS [Hash],
--c.value('(InvoiceData)[1]', 'Datetime') AS [InvoiceData],
--c.value('(/ShipTo/Address/AddressDetail)[1]', 'varchar(50)') AS [genre],
--c.value('(/Line/ProductCode)[1]', 'varchar(20)') AS [Ref]
FROM XmlFile CROSS APPLY Contents.nodes ('(//SourceDocuments/SalesInvoices/Invoice/..)') AS t(c);
I thinks the problem are because i have TAGS before SoureDocuments With attributes, because if i remove theses attributes the query Work:
<AuditFile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:OECD:StandardAuditFile-Tax:PT_1.04_01"> --TAG With Attribute
If i remove this TAG AuditFile and rewrite the others above with only, the query Work
<Country>PT</Country>
<CurrencyCode>EUR</CurrencyCode>
<Header>
<AuditFileVersion>1.04_01</AuditFileVersion>
<CompanyID>501167323</CompanyID>
<TaxRegistrationNumber>501167323</TaxRegistrationNumber>
<TaxAccountingBasis>F</TaxAccountingBasis>
<CompanyName>Planitoi - Importação & Exportação S.A.</CompanyName>
<CompanyAddress>
<BuildingNumber>0</BuildingNumber>
<StreetName>Zona Industrial Do Roligo - Espargo</StreetName>
<AddressDetail>Zona Industrial Do Roligo - Espargo 4524-909 Santa Maria Da Feira</AddressDetail>
<City>Santa Maria Da Feira</City>
<PostalCode>4524-909</PostalCode>
<Region>Desconhecido</Region>
<Country xsi:type="xsd:string">PT</Country> --TAG With Attribute
If i write only this, the query works:
<Country>PT</Country>
</CompanyAddress>
<FiscalYear>2019</FiscalYear>
<StartDate>2019-06-01</StartDate>
<EndDate>2019-06-30</EndDate>
<CurrencyCode xsi:type="xsd:string">EUR</CurrencyCode> --TAG With Attribute
If i write only this, the query works:
<CurrencyCode>EUR</CurrencyCode>
<DateCreated>2019-07-01</DateCreated>
<TaxEntity>GLOBAL</TaxEntity>
<ProductCompanyTaxID>503140600</ProductCompanyTaxID>
<SoftwareCertificateNumber>2616</SoftwareCertificateNumber>
<ProductID>PRIMAVERA JASMIN/PRIMAVERA Business Software Solutions</ProductID>
<ProductVersion>1.00</ProductVersion>
</Header>
Please someone say something.
Best regards,
Luis
July 23, 2019 at 4:44 am
Hello,
I have gone through your query, find the below queries
DECLARE @XML AS XML, @hDoc AS INT, @sql NVARCHAR (MAX)
SELECT @XML = XMLData FROM XMLwithOpenXML
-- specified the namespace which was there in xml file. I have added namespace prefixed 'xx' for parsing the xml data
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<root xmlns:xx="urn:OECD:StandardAuditFile-Tax:PT_1.04_01" />'
--InvoiceNo, InvoiceDate, InvoiceType
SELECT *
FROM OPENXML(@hDoc, 'xx:AuditFile/xx:SourceDocuments/xx:SalesInvoices/xx:Invoice')
WITH
(
NumberOfEntries int '../xx:NumberOfEntries',
TotalCredit decimal(18,2) '../xx:TotalCredit',
InvoiceNo [varchar](50) 'xx:InvoiceNo',
InvoiceDate [varchar](100) 'xx:InvoiceDate',
InvoiceType [varchar](100) 'xx:InvoiceType',
InvoiceStatus [varchar] (2) 'xx:DocumentStatus/xx:InvoiceStatus',
SourceID [varchar] (100) 'xx:DocumentStatus/xx:SourceID',
SelfBillingIndicator [varchar] (2) 'xx:SpecialRegimes/xx:SelfBillingIndicator',
StreetName [varchar] (100) 'xx:ShipTo/xx:Address/xx:StreetName',
Ship_From_StreetName [varchar] (100) 'xx:ShipFrom/xx:Address/xx:StreetName'
)
-- Line numbers, are multiple for one invoice, so selected(treated) as a separate table.
SELECT *
FROM OPENXML(@hDoc, 'xx:AuditFile/xx:SourceDocuments/xx:SalesInvoices/xx:Invoice/xx:Line')
WITH
(
InvoiceNo [varchar](50) '../xx:InvoiceNo',
InvoiceDate [varchar](100) '../xx:InvoiceDate',
LineNumber [varchar] (100) 'xx:LineNumber',
ProductCode [varchar] (100) 'xx:ProductCode'
)
EXEC sp_xml_removedocument @hDoc
Similer way, you can read any node values.
I hope the above solution will solve your problem.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply