Good day, I'm trying to import an XML file from my website (PHP). I have do some research about it and i found this Passing datatable to a stored procedure but that for C#. So, is it possible to send it?
here is my stored Procedure
USE [sales_web]
GO
/****** Object: StoredProcedure [dbo].[InsertSALESPOS] Script Date: 11/30/2016 09:19:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[InsertSALESPOS]
@HeaderTemp HeaderTemp readonly,
@DetailTemp DetailTemp readonly,
@PaymentTemp PaymentTemp readonly,
@VoucherTemp VoucherTemp readonly,
@LedgerTemp LedgerTemp readonly
AS
BEGIN
set nocount on;
begin try
begin transaction
select ID, TransactionNo, TransactionDate, TotalTransaction, LocationID, UserID, CustomerCode, SendStatus
into #HeaderTemp from @HeaderTemp
where TransactionNo not in(SELECT TransactionNo from TransactionHeader with(nolock))
INSERT INTO dbo.SalesOrderHeader (TransactionNo, DocumentNo, TransDate, ETADate, ExternalDocument, Reference, RefTransNo, Category, CustomerCode, Remark, Remark2, TermOfPayment,
Currency, Rate, UseVAT, SubTotal, DiscPercent, DiscAmount, GrandTotal, TotalReceivable, FromCustomerCode, PostingStatus, PostingBy, PostingDate, CreatedDate, CreatedBy, ModifiedDate,
ModifiedBy, CustomerReceivable, LocationCode, VatPercent, VatAmount, FinalReleasedDate_Outlet)
select h.TransactionNo, h.TransactionNo as DocumentNo, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as TransDate,
cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as ETADate, '' as ExternalDocument, '' as Reference, '' as RefTransNo,
'POS' as Category, h.CustomerCode, '' as Remark, '' as Remark2, Cast('' as Datetime) as TermOfPayment, 'IDR' as Currency, Cast(1 as Float) as Rate,
Cast(0 as Bit) as UseVAT, Cast(SUM(d.TotalAmountAfterDisc) as Float) as SubTotal, Cast(0 as Float) as DiscPercent, Cast(0 as Float) as DiscAmount,
Cast(SUM(d.TotalAmountAfterDisc) as Float) as GrandTotal, Cast(SUM(d.TotalAmountAfterDisc) as Float) as TotalReceivable,
h.CustomerCode as FromCustomerCode, Cast(0 as Bit) as PostingStatus, '' as PostingBy, Cast('' as Datetime) as PostingDate,
cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as CreatedDate, h.UserID as CreatedBy,
cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as ModifiedDate, h.UserID as ModifiedBy, h.CustomerCode as CustomerReceivable,
h.CustomerCode as LocationCode, Cast(0 as Float) as VatPercent, Cast(0 as Float) as VatAmount, cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as FinalReleasedDate_Outlet
from @HeaderTemp h
Inner Join @DetailTemp d on h.id = d.TransactionID and h.CustomerCode = d.CustomerCode
Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock))
Group By h.TransactionNo, TransactionDate , h.CustomerCode, h.UserID
Insert Into dbo.SalesOrderDetail
SELECT h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo, h.TransactionNo as SalesOrderTransNo,
h.TransactionNo as RefTransNo, (ROW_NUMBER() Over(Order By h.TransactionNo))*1000 as [LineNo], isnull(d.ItemCode,''), d.Quantity, isnull(d.MainUOMCode,'') as UOM,
d.UnitPrice, 0 as DiscountPercent, 0 as VatPercent, (d.Quantity*d.UnitPrice) as AmountPrice,
((d.Quantity*d.UnitPrice)-d.TotalAmountAfterDisc) as AmountDiscount, 0 as AmountVat,
d.TotalAmountAfterDisc as Total, d.TotalAmountAfterDisc as Receivable,
d.UnitPrice as UnitPriceAfterReceivable, '' as ExtraRemark, '' as ExtraRemark2,
cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as CreatedDate, h.UserID as CreatedBy,
cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime) as ModifiedDate, h.UserID as ModifiedBy
from @HeaderTemp h
Inner Join @DetailTemp d on h.id = d.TransactionID and h.CustomerCode = d.CustomerCode
Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock))
Insert Into dbo.SalesOrderPayment
Select h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo, h.TransactionNo as SalesOrderTransNo,
p.PaymentMethodCode, p.TotalDue, p.TotalTendered
From @HeaderTemp h
Left Join @PaymentTemp p on h.id = p.TransactionID
and h.CustomerCode = p.CustomerCode
Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock))
--cast(left(h.TransactionDate,len(h.TransactionDate)-6) as datetime)
Insert Into dbo.SalesOrderVoucher
Select h.TransactionNo + '-' + Cast(ROW_NUMBER() Over(Order By h.TransactionNo) as Varchar) as TransactionNo, h.TransactionNo as SalesOrderTransNo,
v.VoucherVendor as Voucher, Case When v.VoucherType = '1' Then 'NOMINAL' Else 'PERCENT' End as VoucherType, v.VoucherValue, v.VoucherAmount
From @HeaderTemp h
Left Join @VoucherTemp v On h.CustomerCode = v.CustomerCode And h.ID = v.TransactionID
Where h.TransactionNo IN (select TransactionNo from #HeaderTemp with(nolock))
Insert Into dbo.ItemLedgerEntry(TransactionNo, SequenceNo, DocumentNo, EntryType,
CustomerCode, ItemCode, PostingDate, Quantity, UomCode, CreatedDate, CreatedBy)
Select TransactionNo, SequenceNo, DocumentNo, EntryType,
CustomerCode, ItemCode, cast(left(PostingDate,len(PostingDate)-6) as datetime) as PostingDate, Cast(Quantity as Float) as Quantity, UomCode, cast(left(CreatedDate,len(CreatedDate)-6) as datetime) as CreatedDate, CreatedBy
From @LedgerTemp
Where DocumentNo in (select Transactionno from #HeaderTemp with(nolock))
drop table #HeaderTemp
Select 'Succes' as [Status]
commit transaction
end try
begin catch
rollback
insert into XMLLOGERROR values(getdate(), 'SALESPOS', ERROR_MESSAGE())
end catch
end
I have try this way
$xml=simplexml_load_file($file);
$HeaderTemp = array();
$DetailTemp = array();
$PaymentTemp = array();
$LedgerTemp = array();
$VoucherTemp = array();
$tambahan = array('session_id' => date('d:h:i:s'));
$login = str_replace(' ', '', $this->input->cookie('cookie_webstore_user'));
$session = $login.$tambahan['session_id'];
foreach ($xml->HeaderTemp as $HeaderTempnya)
{
$HeaderTemp[] = $HeaderTempnya;
}
foreach ($xml->DetailTemp as $DetailTempnya)
{
$DetailTemp[] = $DetailTempnya;
}
foreach($xml->PaymentTemp as $PaymentTempnya)
{
$PaymentTemp[] = $PaymentTempnya;
}
foreach($xml->LedgerTemp as $LedgerTempnya)
{
$LedgerTemp[] = $LedgerTempnya;
}
foreach($xml->VoucherTemp as $VoucherTempnya)
{
$VoucherTemp[] = $VoucherTempnya;
}
if($VoucherTempnya != ""){
$this->modelmodel->showsingle("[InsertSALESPOS] '{$HeaderTemp}','{$DetailTemp}','{$PaymentTemp}','{$VoucherTemp}','{$LedgerTemp}'");
}else{
$this->modelmodel->showsingle("[InsertSALESPOS] '{$HeaderTemp}','{$DetailTemp}','{$PaymentTemp}','','{$LedgerTemp}'");
}
With my script above i get this error
Error Number: 22018/206
[Microsoft][SQL Server Native Client 10.0][SQL Server]Operand type clash: varchar is incompatible with HeaderTemp
[InsertSALESPOS] 'Array','Array','Array','','Array'
Filename: D:/xampp/htdocs/new_store/system/database/DB_driver.php
Line Number: 691
Sorry for my bad english.
Ok. I have solved my problem above with different way. I'm just curious about it if impossible, please explain why.
You don't have to use PHP as it can be directly inserted through MySQL. The command would be like
LOAD XML LOCAL INFILE '/pathtofile/file.xml'
INTO TABLE my_tablename(col1, col2, ...);
For More information about Loading XML data read Mysql Docs here
It is possible with openrowset command to load the xml into a table in sql server, for more information go through the link https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/
It looks like you are trying to use an array as a value for a field, where the field type is defined as a varchar.
You should loop true the array you are now trying to use for insert and execute the insert statement in each loop using the current value in the loop