SQL2005 通过xml字符串批量插入主从表

Question

<?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>
以上是xml字符串
我想插入到两个表：Owner 和 Finger
这两张表的主键都是自动增长ID：OwnerID  和  FingerID
其中表【Finger】中还有个外键【OwnerID】与主表关联请问如何在过程中实现？？sql2005XML主从表

Answer 1

可以用一张表生产ID插入的主从表都用这个ID

Answer 2

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, @docSELECT *
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区分之后再插入表中，但都很麻烦

Answer 3

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 FingerEXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;SELECT * FROM [Owner]
SELECT * FROM FingerDROP 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
*/

Answer 4

先插入主表，通过插入的数据与xml打开的表关联，得到主表的自增ID，由此ID插入到子表如果还不行，你就把你的表结构帖出来

Answer 5

这个要在你解析的时候就要关联好了才能插入，并非随便插入就行
因为区分主从，插入后再来关联就乱了，而且用XML也不一定就几条数据吧，多了呢？

Answer 6

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
)

Answer 7

--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 NULLFinger_id Owner_id FingerCode ImagePath
1 1 1
2 1 22
3 1 2233
4 2 22
5 2 2233
*/

Answer 8

十分感谢我再问一下：如果在添加的时候，需要增加是否存在的验证，应该怎么处理？例如：我现在批量传了两个Owner，每个Owner分别传了两个Finger，再做插入的时候，发现其中一个Owner已存在，但是其Finger不存在，或者只存在一条信息，这时应该怎么处理呢？要求就是：如果已经存在的就不做处理，否则就添加
Owner 的唯一性是 CardNum
Finger 的唯一性是 FingerCode

Answer 9

连续执行两次下面SQL，你会发现，第二次因为已经存在，就不再插入了：
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
)

Answer 10

十分感谢但是如果Owner001 已经存在，但是其指纹信息不存在，这时就应该先获取 Owner001 的 OwnerID，将其插入到 Finger 表里
请问这应该什么处理？？

Answer 11

SQL的最后加上下面这行
--加上这一行
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
)

Answer 12

那也有可能，我没了解楼主的意思。
总之，这个SQL并不难。主要是XML的解析及OUTPUT的用法。楼主如果是看懂了，我相信已经可以自行解决了。呵呵

Answer 13

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 就漏掉了

Answer 14

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 rowidINSERT 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