XML的属性如何用openxml的方式写入数据库中???

Question

我用openxml的方式可以把xml存入到数据库中，但现在有一个问题xml某一结点有属性的话我应该如何读取呢？比如<Title id="1">XML Application Development with MSXML 4.0</Title> 在这里面的id应该如何读取并存入到数据库中呢？？？？？用openxml的方式！谢谢！

Answer 1

最后一个参数我写了8，是空。出现了如下提示：
Server: Msg 515, Level 16, State 2, Procedure sp_Insert_RETest_Openxml, Line 6
Cannot insert the value NULL into column 'PropertyName', table 'Northwind.dbo.RETest'; column does not allow nulls. INSERT fails.
The statement has been terminated.也就是说属性值还是不能写入数据库中

Answer 2

DECLARE @idoc int,@doc varchar(8000)SET @doc ='<Title id="1">XML Application Development with MSXML 4.0</Title>'EXEC sp_xml_preparedocument @idoc OUTPUT, @doc--第一层
SELECT * FROM OPENXML (@idoc, '/Title',2)
WITH (
id int '@id',
Title varchar(50) '.')
EXEC sp_xml_removedocument @idoc-- 结果:
id          Title
----------- --------------------------------------------------
1           XML Application Development with MSXML 4.0(1 行受影响)

Answer 3

不知道楼主的openxml是否指sql中的, 如果是的话, 则在定义列的时候, 可以在后面指定 xpath, 这样只要xpath正确, 你想怎么取都行.

Answer 4

存储过程：
CREATE PROCEDURE sp_Insert_Test_Openxml
    @strXML ntext
AS
    DECLARE @iDoc int
    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML
    INSERT INTO RETest (title,publisher,DateOfPurchase,author)
      (SELECT * FROM OpenXML(@iDoc, 'books/book',8)
                WITH 
               (title VARCHAR(100), 
            publisher VARCHAR(100),
            DateOfPurchase VARCHAR(100),
            author varchar(100))
      )
    EXECUTE sp_xml_removedocument @iDoc下面是xml的内容:
<?xml version="1.0"?>
<Books>
      <Book>
            <Title author="aa">XML Application Development with MSXML 4.0</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>2/1/2002</DateOfPurchase>
      </Book>
      <Book>
            <Title author="bb">Professional SQL Server 2000 XML</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>9/10/2001</DateOfPurchase>
      </Book>
      <Book>
            <Title author="cc">Professional XML for .NET Developers</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>12/20/2001</DateOfPurchase>
      </Book>
</Books>帮我看看那里有问题，为什么出现了:Server: Msg 515, Level 16, State 2, Procedure sp_Insert_RETest_Openxml, Line 6
Cannot insert the value NULL into column author, table 'Northwind.dbo.RETest'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Answer 5

CREATE PROCEDURE sp_Insert_Test_Openxml
    @strXML ntext
AS
    DECLARE @iDoc int
    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML
--    INSERT INTO RETest (title,publisher,DateOfPurchase,author)
      SELECT * FROM OpenXML(@iDoc, 'Books/Book',2)
                WITH 
               (title VARCHAR(100) 'Title', 
            publisher VARCHAR(100) 'Publisher',
            DateOfPurchase VARCHAR(100),
            author varchar(100) 'Title/@author')
      
    EXECUTE sp_xml_removedocument @iDoc
GOEXEC sp_Insert_Test_Openxml N'
<?xml version="1.0"?>
<Books>
      <Book>
            <Title author="aa">XML Application Development with MSXML 4.0</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>2/1/2002</DateOfPurchase>
      </Book>
      <Book>
            <Title author="bb">Professional SQL Server 2000 XML</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>9/10/2001</DateOfPurchase>
      </Book>
      <Book>
            <Title author="cc">Professional XML for .NET Developers</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>12/20/2001</DateOfPurchase>
      </Book>
</Books>
'
GODROP PROC sp_Insert_Test_Openxml-- 结果:
title publisher DateOfPurchase authorXML Application Development with MSXML 4.0 Wrox Press 2/1/2002 aa
Professional SQL Server 2000 XML Wrox Press 9/10/2001 bb
Professional XML for .NET Developers Wrox Press 12/20/2001 cc

Answer 6

1. xml的解析是区分大小写的
2. 如果属性不在openxml当前指定的xpath下, 要指定xpath

Answer 7

小弟新手，路过帮定，
顺便问一下，什么是 openxml ？

Answer 8

zjcxc(邹建) ？？？
难道是马甲？？

Answer 9

那这样的xml应该如何读取？？？也就是说有两个标记名一样的应该如何存入数据库中？
<?xml version="1.0"?>
<Books>
      <Book>
            <Title author="aa">XML Application Development with MSXML 4.0</Title>
            <Title author="aa">XML应用开发</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>2/1/2002</DateOfPurchase>
      </Book>
      <Book>
            <Title author="bb">Professional SQL Server 2000 XML</Title>
            <Title author="bb">标准SQL Server 2000 xml</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>9/10/2001</DateOfPurchase>
      </Book>
      <Book>
            <Title author="cc">Professional XML for .NET Developers</Title>
            <Title author="cc">开发者之xml</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>12/20/2001</DateOfPurchase>
      </Book>
</Books>

Answer 10

想把两个title放在数据库中同一个字段中，应该如何做？

Answer 11

只是想把两个名称相同的tag放在数据库中同一个字段当中，应该如何去做？？

Answer 12

<Title author="cc">Professional XML for .NET Developers</Title>
            <Title author="cc">开发者之xml</Title>放在一起？

Answer 13

CREATE PROCEDURE sp_Insert_Test_Openxml
    @strXML ntext
AS
    DECLARE @iDoc int
    EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @strXML
--    INSERT INTO RETest (title,publisher,DateOfPurchase,author)
      SELECT * FROM OpenXML(@iDoc, 'Books/Book',2)
                WITH 
               (
title1 VARCHAR(100) 'Title[1]', 
title2 VARCHAR(100) 'Title[2]', 
            publisher VARCHAR(100) 'Publisher',
            DateOfPurchase VARCHAR(100),
            author varchar(100) 'Title/@author')
      
    EXECUTE sp_xml_removedocument @iDoc
GOEXEC sp_Insert_Test_Openxml N'
<?xml version="1.0"?>
<Books>
      <Book>
            <Title author="aa">XML Application Development with MSXML 4.0</Title>
            <Title author="aa">XML应用开发</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>2/1/2002</DateOfPurchase>
      </Book>
      <Book>
            <Title author="bb">Professional SQL Server 2000 XML</Title>
            <Title author="bb">标准SQL Server 2000 xml</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>9/10/2001</DateOfPurchase>
      </Book>
      <Book>
            <Title author="cc">Professional XML for .NET Developers</Title>
            <Title author="cc">开发者之xml</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>12/20/2001</DateOfPurchase>
      </Book>
</Books>
'
GODROP PROC sp_Insert_Test_Openxml-- 结果:

Answer 14

<Books>
      <Book>
            <Title author="aa">XML Application Development with MSXML 4.0</Title>
            <Title author="aa">XML应用开发</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>2/1/2002</DateOfPurchase>
            <contacts>
              <contact>XXX</contact>
            </contacts>
      </Book>
      <Book>
            <Title author="bb">Professional SQL Server 2000 XML</Title>
            <Title author="bb">标准SQL Server 2000 xml</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>9/10/2001</DateOfPurchase>
              <contacts>
              <contact>YYY</contact>
            </contacts>
      </Book>
      <Book>
            <Title author="cc">Professional XML for .NET Developers</Title>
            <Title author="cc">开发者之xml</Title>
            <Publisher>Wrox Press</Publisher>
            <DateOfPurchase>12/20/2001</DateOfPurchase>
              <contacts>
              <contact>ZZZ</contact>
            </contacts>
      </Book>
</Books>上面的这个xml，我想这个contact这个结点的内容也同时读出来，上面的存储过程这句SELECT * FROM OpenXML(@iDoc, 'Books/Book',2)我应该怎么写？还是应该在WITH  (
title1 VARCHAR(100) 'Title[1]', 
title2 VARCHAR(100) 'Title[2]', 
            publisher VARCHAR(100) 'Publisher',
            DateOfPurchase VARCHAR(100),
            author varchar(100) 'Title/@author'            contact varchar(100) '这里应该加什么？')应该怎么做

Answer 15

楼主难道连一点规律都看不出来?OpenXML(@iDoc, 'Books/Book',2)我应该怎么写？还是应该在WITH  (
title1 VARCHAR(100) 'Title[1]', 
title2 VARCHAR(100) 'Title[2]', 
            publisher VARCHAR(100) 'Publisher',
            DateOfPurchase VARCHAR(100),
            author varchar(100) 'Title/@author',
            contact varchar(100) 'contacts/contact')