将数据写入xml然后插入table的过程

if (null != ds && ds.Tables.Count > 0)
                    {
                        sb.Append("<?xml version=\"1.0\" encoding=\"gb2312\" ?><ROOT>");
                        DataTable dt = ds.Tables[0];
                        if (null != dt && dt.Rows.Count > 0)
                        {
                            foreach (DataRow item in dt.Rows)
                            {
                                sb.Append("<NC>");
                                sb.AppendFormat("<PK_CoRP>{0}</PK_CoRP>", item["PK_CoRP"].ToString());
                                sb.AppendFormat("<deptcode>{0}</deptcode>", item["deptcode"].ToString());
                                sb.AppendFormat("<fyx>{0}</fyx>", item["fyx"].ToString());
                                sb.AppendFormat("<je>{0}</je>", item["je"].ToString());
                                sb.Append("</NC>");
                            }
                        }
                        else
                        {
                            sb.Append("<NC>");
                            sb.AppendFormat("<PK_CoRP>{0}</PK_CoRP>", "");
                            sb.AppendFormat("<deptcode>{0}</deptcode>", "");
                            sb.AppendFormat("<fyx>{0}</fyx>", "");
                            sb.AppendFormat("<je>{0}</je>", "");
                            sb.Append("</NC>");
                        }
                        sb.Append("</ROOT>");
                    }

 

ALTER PROCEDURE [dbo].[YD_FY_getYSDX]
@m_XML text ,    --NC查过来的数据
@FYXMDZID VARCHAR(200)  --预算对象ID
AS

declare @hdoc int
 exec sp_xml_preparedocument @hdoc output,@m_XML
 --把插过来的数据存入临时表中
 
SELECT * INTO ##NCDBYSDX
FROM OPENXML (@hdoc, '/ROOT/NC',1)
WITH (PK_CoRP int 'PK_CoRP',
deptcode varchar(50) 'deptcode',
fyx varchar(50)  'fyx',
je FLOAT 'je')

declare @sql varchar(8000)
select @sql ='SELECT FYXMDZID AS initID,BMXXID AS DeptID,DeptName,RYXXID AS UserID,UserName,NCDEPTCODE AS NC_YS,
NCDeptName AS NC_YSName, a.PK_CORP AS NC_JGID,PK_DEPTDOC AS NC_DeptID ,NCUNITCODE AS NC_JGNO,
NCUNITNAME AS NC_JGName,d.JE as LPYSJE,e.JE as YPYSJE
FROM dbo.YD_FY_YuSuanDuiZhao a
LEFT OUTER JOIN dbo.Department b ON b.DeptID = a.BMXXID
LEFT OUTER JOIN  dbo.Users c ON c.UserID = a.RYXXID
left outer join ##NCDBYSDX d on (a.PK_CORP=d.PK_CORP and a.NCDEPTCODE=d.DEPTCODE and d.FYX=''领用礼品'')
left outer join ##NCDBYSDX e on (a.PK_CORP=e.PK_CORP and a.NCDEPTCODE=e.DEPTCODE and e.FYX=''办公用品'')
where FYXMDZID='''+@FYXMDZID+''''
--PRINT @sql
 exec(@sql)
if object_id('tempdb..##NCDBYSDX') is not NULL
DROP TABLE ##NCDBYSDX

转载于:https://www.cnblogs.com/wuquelin/articles/3089887.html