22,209
社区成员
发帖
与我相关
我的任务
分享
<?xml version="1.0" encoding="gb2312"?>
<Owners>
<Owner Name="1" Sex="2" CardNum="3" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="1" ImagePath="" />
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
<Owner Name="11" Sex="2" CardNum="32" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
</Owners>
--加上这一行
INSERT INTO TABLE_Finger(Owner_id, FingerCode, ImagePath)
SELECT Owner_id, 0, '' FROM @no b
WHERE NOT EXISTS
(
SELECT 1
FROM TABLE_Finger x
WHERE x.Owner_id = b.Owner_id
)
DECLARE @doc VARCHAR(MAX), @xml XML
SET @doc =N'<?xml version="1.0" encoding="gb2312"?>
<Owners>
<Owner Name="1" Sex="2" CardNum="3" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="1" ImagePath="" />
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
<Owner Name="11" Sex="2" CardNum="32" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
</Owners>
'
SET @xml = REPLACE(@doc, '<?xml version="1.0" encoding="gb2312"?>', '')
DECLARE @no TABLE(Owner_id int)
DECLARE @temp TABLE(rowid INT, NAME VARCHAR(100), Sex INT, CardNum INT, Nation INT, Birth INT, EffectiveDate INT, [Address] INT, CardPath VARCHAR(100), PhotoPath VARCHAR(100), subXml xml)
INSERT @temp
SELECT
rowid = ROW_NUMBER() OVER(ORDER BY GETDATE()),
T.c.value('(./@Name)[1]', 'varchar(100)'),
T.c.value('(./@Sex)[1]', 'int'),
T.c.value('(./@CardNum)[1]', 'int'),
T.c.value('(./@Nation)[1]', 'int'),
T.c.value('(./@Birth)[1]', 'int'),
T.c.value('(./@EffectiveDate)[1]', 'int'),
T.c.value('(./@Address)[1]', 'int'),
T.c.value('(./@CardPath)[1]', 'varchar(100)'),
T.c.value('(./@PhotoPath)[1]', 'varchar(100)'),
[subXml] = T.c.query('./Finger')
FROM @xml.nodes('Owners/Owner') AS T(c)
INSERT INTO TABLE_Owner(NAME,Sex,CardNum,Nation,Birth,EffectiveDate,[Address],CardPath,PhotoPath)
OUTPUT INSERTED.Owner_id
INTO @no(Owner_id)
SELECT NAME,Sex,CardNum,Nation,Birth,EffectiveDate,[Address],CardPath,PhotoPath
FROM @temp y
WHERE NOT EXISTS --插入前加判断即可
(
SELECT 1
FROM TABLE_Owner x
WHERE x.CardNum = y.CardNum
)
ORDER BY rowid
INSERT INTO TABLE_Finger(Owner_id, FingerCode, ImagePath)
SELECT
b.Owner_id, c.FingerCode, c.ImagePath
FROM (SELECT rowid, subXml FROM @temp) a
INNER JOIN (SELECT rowid=ROW_NUMBER() OVER(ORDER BY owner_id),Owner_id FROM @no) b
ON a.rowid = b.rowid
CROSS APPLY
(
SELECT
FingerCode = T.c.value('(./@FingerCode)[1]', 'int'),
ImagePath = T.c.value('(./@ImagePath)[1]', 'varchar(100)')
FROM a.subXml.nodes('Finger') AS T(c)
) c
WHERE NOT EXISTS --插入前加判断即可
(
SELECT 1
FROM TABLE_Finger x
WHERE x.Owner_id = b.Owner_id
AND x.FingerCode = c.FingerCode
)
DECLARE @doc VARCHAR(MAX), @xml XML
SET @doc =N'<?xml version="1.0" encoding="gb2312"?>
<Owners>
<Owner Name="1" Sex="2" CardNum="3" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="1" ImagePath="" />
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
<Finger FingerCode="8888" ImagePath="" />
</Owner>
<Owner Name="11" Sex="2" CardNum="32" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
<Finger FingerCode="9999" ImagePath="" />
</Owner>
</Owners>
'
SET @xml = REPLACE(@doc, '<?xml version="1.0" encoding="gb2312"?>', '')
DECLARE @no TABLE(Owner_id int)
DECLARE @temp TABLE(rowid INT, NAME VARCHAR(100), Sex INT, CardNum INT, Nation INT, Birth INT, EffectiveDate INT, [Address] INT, CardPath VARCHAR(100), PhotoPath VARCHAR(100), subXml xml)
INSERT @temp
SELECT
rowid = ROW_NUMBER() OVER(ORDER BY GETDATE()),
T.c.value('(./@Name)[1]', 'varchar(100)'),
T.c.value('(./@Sex)[1]', 'int'),
T.c.value('(./@CardNum)[1]', 'int'),
T.c.value('(./@Nation)[1]', 'int'),
T.c.value('(./@Birth)[1]', 'int'),
T.c.value('(./@EffectiveDate)[1]', 'int'),
T.c.value('(./@Address)[1]', 'int'),
T.c.value('(./@CardPath)[1]', 'varchar(100)'),
T.c.value('(./@PhotoPath)[1]', 'varchar(100)'),
[subXml] = T.c.query('./Finger')
FROM @xml.nodes('Owners/Owner') AS T(c)
INSERT INTO TABLE_Owner(NAME,Sex,CardNum,Nation,Birth,EffectiveDate,[Address],CardPath,PhotoPath)
OUTPUT INSERTED.Owner_id
INTO @no(Owner_id)
SELECT NAME,Sex,CardNum,Nation,Birth,EffectiveDate,[Address],CardPath,PhotoPath
FROM @temp y
WHERE NOT EXISTS --插入前加判断即可
(
SELECT 1
FROM TABLE_Owner x
WHERE x.CardNum = y.CardNum
)
ORDER BY rowid
INSERT INTO TABLE_Finger(Owner_id, FingerCode, ImagePath)
SELECT a.Owner_id, c.FingerCode, c.ImagePath
FROM (SELECT rowid, Owner_id, subXml FROM (SELECT a.*, b.Owner_id FROM @temp a INNER JOIN TABLE_Owner b ON a.CardNum = b.CardNum) t) a
CROSS APPLY
(
SELECT
FingerCode = T.c.value('(./@FingerCode)[1]', 'int'),
ImagePath = T.c.value('(./@ImagePath)[1]', 'varchar(100)')
FROM a.subXml.nodes('Finger') AS T(c)
) c
WHERE NOT EXISTS --插入前加判断即可
(
SELECT 1
FROM TABLE_Finger x
WHERE x.Owner_id = a.Owner_id
AND x.FingerCode = c.FingerCode
)
--查看结果
SELECT * from TABLE_owner
SELECT * from TABLE_Finger
INSERT INTO TABLE_Owner(NAME,Sex,CardNum,Nation,Birth,EffectiveDate,[Address],CardPath,PhotoPath)
OUTPUT INSERTED.Owner_id
INTO @no(Owner_id)
SELECT NAME,Sex,CardNum,Nation,Birth,EffectiveDate,[Address],CardPath,PhotoPath
FROM @temp y
WHERE NOT EXISTS --插入前加判断即可
(
SELECT 1
FROM TABLE_Owner x
WHERE x.CardNum = y.CardNum
)
ORDER BY rowid
这一句是验证了重复的不添加,但是 @no 里也没能获取已经存在的OwnerID,这样再插入Finger时,如果其Owner已经存在,就无法插入新的Finger数据
例如:
1、现在Owner表里有一条数据A,Finger表里没有数据;现在传了一段xml需要执行这个过程
2、xml里含有两条Owner:A和B;3条Finger:A-1,B-1,B-2
3、如果按照上面的过程执行,结果就是:插入了一条Owner:B 和 两条 Finger:B-1,B-2
4、这样Finger:A-1 就漏掉了--table
CREATE TABLE TABLE_owner (Owner_id INT IDENTITY PRIMARY KEY, NAME VARCHAR(100), Sex INT, CardNum INT, Nation INT, Birth INT, EffectiveDate INT, [Address] INT, CardPath VARCHAR(100), PhotoPath VARCHAR(100))
CREATE TABLE TABLE_Finger (Finger_id INT IDENTITY PRIMARY KEY, Owner_id INT, FingerCode INT, ImagePath VARCHAR(100))
--test
DECLARE @doc VARCHAR(MAX), @xml XML
SET @doc =N'<?xml version="1.0" encoding="gb2312"?>
<Owners>
<Owner Name="1" Sex="2" CardNum="3" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="1" ImagePath="" />
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
<Owner Name="11" Sex="2" CardNum="32" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
</Owners>
'
SET @xml = REPLACE(@doc, '<?xml version="1.0" encoding="gb2312"?>', '')
DECLARE @no TABLE(Owner_id int)
DECLARE @temp TABLE(rowid INT, NAME VARCHAR(100), Sex INT, CardNum INT, Nation INT, Birth INT, EffectiveDate INT, [Address] INT, CardPath VARCHAR(100), PhotoPath VARCHAR(100), subXml xml)
INSERT @temp
SELECT
rowid = ROW_NUMBER() OVER(ORDER BY GETDATE()),
T.c.value('(./@Name)[1]', 'varchar(100)'),
T.c.value('(./@Sex)[1]', 'int'),
T.c.value('(./@CardNum)[1]', 'int'),
T.c.value('(./@Nation)[1]', 'int'),
T.c.value('(./@Birth)[1]', 'int'),
T.c.value('(./@EffectiveDate)[1]', 'int'),
T.c.value('(./@Address)[1]', 'int'),
T.c.value('(./@CardPath)[1]', 'varchar(100)'),
T.c.value('(./@PhotoPath)[1]', 'varchar(100)'),
[subXml] = T.c.query('./Finger')
FROM @xml.nodes('Owners/Owner') AS T(c)
INSERT INTO TABLE_Owner(NAME,Sex,CardNum,Nation,Birth,EffectiveDate,[Address],CardPath,PhotoPath)
OUTPUT INSERTED.Owner_id
INTO @no(Owner_id) --假设Owner_id是Owner表的自增列
SELECT NAME,Sex,CardNum,Nation,Birth,EffectiveDate,[Address],CardPath,PhotoPath
FROM @temp
ORDER BY rowid
INSERT INTO TABLE_Finger(Owner_id, FingerCode, ImagePath)
SELECT
b.Owner_id, c.FingerCode, c.ImagePath
FROM (SELECT rowid, subXml FROM @temp) a
INNER JOIN (SELECT rowid=ROW_NUMBER() OVER(ORDER BY owner_id),Owner_id FROM @no) b
ON a.rowid = b.rowid
CROSS APPLY
(
SELECT
FingerCode = T.c.value('(./@FingerCode)[1]', 'int'),
ImagePath = T.c.value('(./@ImagePath)[1]', 'varchar(100)')
FROM a.subXml.nodes('Finger') AS T(c)
) c
--结果
SELECT * FROM table_Owner
SELECT * FROM table_Finger
/*
Owner_id NAME Sex CardNum Nation Birth EffectiveDate Address CardPath PhotoPath subXml
1 1 2 3 4 6 5 7 NULL
2 11 2 32 4 6 5 7 NULL
Finger_id Owner_id FingerCode ImagePath
1 1 1
2 1 22
3 1 2233
4 2 22
5 2 2233
*/
CREATE TABLE [dbo].[T_Owner](
[OwnerId] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](10) NULL,
[Sex] [char](2) NULL,
[Nation] [varchar](20) NULL,
[Birth] [varchar](15) NULL,
[Address] [varchar](100) NULL,
[CardNum] [varchar](20) NULL,
[EffectiveDate] [varchar](50) NULL,
[CardPath] [varchar](100) NULL,
[PhotoPath] [varchar](100) NULL
)
CREATE TABLE [dbo].[T_Finger](
[FingerId] [int] IDENTITY(1,1) NOT NULL,
[OwnerId] [int] NULL,
[ImagePath] [varchar](100) NULL,
[FingerCode] [varchar](100) NULL
)
create table [Owner](Name varchar(5),
Sex varchar(5),
CardNum varchar(5),
Nation varchar(5),
Birth varchar(5),
EffectiveDate varchar(5),
Address varchar(5),
CardPath varchar(5),
PhotoPath varchar(5))
create table Finger(FingerCode varchar(5),ImagePath varchar(50))
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<Owners>
<Owner Name="1" Sex="2" CardNum="3" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="1" ImagePath="" />
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
<Owner Name="11" Sex="2" CardNum="32" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
</Owners>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
INSERT INTO [Owner]
SELECT *
FROM OPENXML(@docHandle, N'/Owners/Owner') WITH [Owner]
INSERT INTO Finger
SELECT *
FROM OPENXML(@docHandle, N'//Finger') WITH Finger
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SELECT * FROM [Owner]
SELECT * FROM Finger
DROP TABLE Finger,[Owner]
/*
Name Sex CardNum Nation Birth EffectiveDate Address CardPath PhotoPath
1 2 3 4 6 5 7
11 2 32 4 6 5 7
*/
/*
Finger ImagePath
1
22
2233
22
2233
*/
DECLARE @idoc INT=0
DECLARE @doc VARCHAR(1000)
SET @doc =N'<?xml version="1.0" encoding="gb2312"?>
<Owners>
<Owner Name="1" Sex="2" CardNum="3" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="1" ImagePath="saf" />
<Finger FingerCode="22" ImagePath="sdf" />
<Finger FingerCode="2233" ImagePath="fs" />
</Owner>
<Owner Name="11" Sex="2" CardNum="32" Nation="4" Birth="6" EffectiveDate="5" Address="7" CardPath="" PhotoPath="">
<Finger FingerCode="22" ImagePath="" />
<Finger FingerCode="2233" ImagePath="" />
</Owner>
</Owners>
'
EXEC SP_XML_PREPAREDOCUMENT @idoc OUTPUT, @doc
SELECT *
FROM OPENXML (@idoc, 'Owners/Owner',1)
WITH (
Name NVARCHAR(1000),
Sex NVARCHAR(1000),
CardNum VARCHAR,
Nation VARCHAR,
Birth VARCHAR,
EffectiveDate VARCHAR,
Address VARCHAR ,
CardPath VARCHAR,
PhotoPath VARCHAR )
SELECT *
FROM OPENXML(@idoc,'Owners/Owner/Finger',1)
WITH (
FingerCode CHAR ,
ImagePath NVARCHAR(1000)
)
可以这样得到数据,但是插入就比较麻烦,得区分主从,因为你用的自增列做主外键,那就要插入主表后返回ID,再插入从表,我的想法要么改改XML,或者用Xquery插入ID区分之后再插入表中,但都很麻烦