Is it possible to send XML format with PHP to sql server

Question

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.

Answer 1

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/