是否可以用PHP向sql server发送XML格式？

Question

你好，我正在尝试从我的网站(PHP)导入一个XML文件。我已经对它做了一些研究，我找到了这个将数据表传递给存储过程，但这是为C#。那么，有可能寄出去吗？

这是我的存储过程

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     

我已经试过了

$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}'");
                    }

通过上面的脚本，我得到了这个错误

错误编号:22018/206号 MicrosoftSQL ServerOperand类型冲突: varchar与HeaderTemp不兼容 InsertSALESPOS 'Array‘、'’、'Array‘ 文件名: D:/xampp/htdocs/new_store/system/database/DB_driver.php 线路号码: 691

对不起我的英语不好。

好的。我已经用不同的方式解决了我的问题。我只是好奇如果不可能的话，请解释一下原因。

Answer 1

可以使用openrowset命令将xml加载到sql server中的表中，以便通过链接https://www.mssqltips.com/sqlservertip/2899/importing-and-processing-data-from-xml-files-into-sql-server-tables/获得更多信息。

Answer 2

看起来，您正在尝试使用数组作为字段的值，其中字段类型被定义为varchar。

应该循环当前要用于插入的数组，并使用循环中的当前值在每个循环中执行insert语句。

Answer 3

您不必使用PHP，因为它可以通过MySQL直接插入。命令就像

LOAD XML LOCAL INFILE '/pathtofile/file.xml' 
INTO TABLE my_tablename(col1, col2, ...); 

有关加载XML数据的更多信息，请阅读Mysql文档这里。