查询SQL Server 2012中存储为xml的文档

Question

我一直在尝试学习Open XML，并在Visual Studio c#中成功地生成了一个免费的文本2007 .docx文件。之后，我将文件以.xml格式保存在Word中，然后将.xml文件导入到sql server2012中的一个名为XML的表中，导入到一个名为xml的列中:以下是该表的代码：

CREATE TABLE [dbo].[XML](
    [XML_ID] [int] IDENTITY(1,1) NOT NULL,
    [XML] [xml] NOT NULL,
 CONSTRAINT [PK_XML] PRIMARY KEY CLUSTERED 
(
    [XML_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

下面是我运行的插入代码

INSERT INTO XML(XML)
SELECT * FROM OPENROWSET(
   BULK 'C:\Users\bbt2d\Desktop\Document.xml',
   SINGLE_BLOB) AS x;

当我运行select *时，xml文件出现了，但是当我试图查询段落中的一些数据时，我没有得到任何结果。

下面是存储在sql server中的xml的部分截图:我想知道我是不是搞错了这个过程，或者您将如何从文档中查询文本。

?mso-application progid="Word.Document"?>
<pkg:package xmlns:pkg="http://schemas.microsoft.com/office/2006/xmlPackage">
  <pkg:part pkg:name="/_rels/.rels" pkg:contentType="application/vnd.openxmlformats-package.relationships+xml" pkg:padding="512">
    <pkg:xmlData>
      <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
        <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml" />
        <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml" />
        <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="word/document.xml" />
      </Relationships>
    </pkg:xmlData>
  </pkg:part>
  <pkg:part pkg:name="/word/_rels/document.xml.rels" pkg:contentType="application/vnd.openxmlformats-package.relationships+xml" pkg:padding="256">
    <pkg:xmlData>
      <Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
        <Relationship Id="rId8" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/fontTable" Target="fontTable.xml" />
        <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/settings" Target="settings.xml" />
        <Relationship Id="rId7" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/footer" Target="footer1.xml" />
        <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml" />
        <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/numbering" Target="numbering.xml" />
        <Relationship Id="rId6" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/endnotes" Target="endnotes.xml" />
        <Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/footnotes" Target="footnotes.xml" />
        <Relationship Id="rId10" Type="http://schemas.microsoft.com/office/2007/relationships/stylesWithEffects" Target="stylesWithEffects.xml" />
        <Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/webSettings" Target="webSettings.xml" />
        <Relationship Id="rId9" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml" />
      </Relationships>
    </pkg:xmlData>
  </pkg:part>
  <pkg:part pkg:name="/word/document.xml" pkg:contentType="application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml">
    <pkg:xmlData>
      <w:document xmlns:ve="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:m="http://schemas.openxmlformats.org/officeDocument/2006/math" xmlns:v="urn:schemas-microsoft-com:vml" xmlns:wp="http://schemas.openxmlformats.org/drawingml/2006/wordprocessingDrawing" xmlns:w10="urn:schemas-microsoft-com:office:word" xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main" xmlns:wne="http://schemas.microsoft.com/office/word/2006/wordml">
        <w:body>
          <w:p w:rsidR="00C32D34" w:rsidRDefault="00C32D34">
            <w:pPr>
              <w:pStyle w:val="Heading1" />
              <w:spacing w:line="240" w:lineRule="auto" />
              <w:jc w:val="center" />
              <w:rPr>
                <w:rFonts w:ascii="Times New Roman" w:hAnsi="Times New Roman" />
                <w:sz w:val="44" />
                <w:szCs w:val="44" />
              </w:rPr>
            </w:pPr>
            <w:r>
              <w:rPr>
                <w:rFonts w:ascii="Times New Roman" w:hAnsi="Times New Roman" />
                <w:sz w:val="44" />
                <w:szCs w:val="44" />
              </w:rPr>
              <w:t>School District of Marshfield</w:t>
            </w:r>
          </w:p>
          <w:p w:rsidR="00C32D34" w:rsidRDefault="00C32D34">
            <w:pPr>
              <w:autoSpaceDE w:val="0" />
              <w:autoSpaceDN w:val="0" />
              <w:adjustRightInd w:val="0" />
              <w:spacing w:after="0" w:line="240" w:lineRule="auto" />
              <w:rPr>
                <w:rFonts w:ascii="Cambria" w:hAnsi="Cambria" w:cs="Cambria" />
                <w:b />
                <w:bCs />
                <w:sz w:val="28" />
                <w:szCs w:val="28" />
              </w:rPr>
            </w:pPr>
          </w:p>
          <w:p w:rsidR="00C32D34" w:rsidRDefault="00DE219B">
            <w:pPr>
              <w:autoSpaceDE w:val="0" />
              <w:autoSpaceDN w:val="0" />
              <w:adjustRightInd w:val="0" />
              <w:spacing w:after="0" w:line="240" w:lineRule="auto" />
              <w:jc w:val="center" />
              <w:rPr>
                <w:rFonts w:ascii="Cambria" w:hAnsi="Cambria" w:cs="Cambria" />
                <w:b />
                <w:bCs />
                <w:sz w:val="28" />
                <w:szCs w:val="28" />
              </w:rPr>
            </w:pPr>
            <w:r>
              <w:rPr>
                <w:rFonts w:ascii="Cambria" w:hAnsi="Cambria" w:cs="Cambria" />
                <w:b />
                <w:bCs />
                <w:sz w:val="28" />
                <w:szCs w:val="28" />
              </w:rPr>
              <w:t>Exploring French</w:t>
            </w:r>
            <w:r w:rsidR="00C32D34">
              <w:rPr>
                <w:rFonts w:ascii="Cambria" w:hAnsi="Cambria" w:cs="Cambria" />
                <w:b />
                <w:bCs />
                <w:sz w:val="28" />
                <w:szCs w:val="28" />
              </w:rPr>
              <w:t xml:space="preserve"> &amp; Spanish</w:t>
            </w:r>

Answer 1

您发布的XML是不完整的-假设您的表的XML是完整的，您可以尝试类似这样的操作(顺便说一句:我建议不要对您的列使用保留字；不要将您的列称为XML -这是保留关键字；使用XmlDoc或类似的东西...)

-- define the two XML namespaces we need to use to get to the data in question
;WITH XMLNAMESPACES ('http://schemas.microsoft.com/office/2006/xmlPackage' as pkg, 
                     'http://schemas.openxmlformats.org/wordprocessingml/2006/main' as w)
select
    XmlContent.value('(//w:t)[2]', 'varchar(100)')
FROM dbo.YourTable
where ....(some condition here).....

真正有趣的部分是找出正确的XPath表达式来获取数据。不幸的是，您感兴趣的文本似乎不在特定标记的段落中，例如，它周围的<w:r>和<w:rPr>标记没有任何您可以检查的特定属性-所以您所能做的就是找出它是您感兴趣的文档中的第二个<w:t>节点，并以这种方式转到它的文本。

Answer 2

当你在sql server中处理XML数据类型时，我总是建议你使用Xquery，下面的url让你对Xquery有了清晰的理解。http://www.youtube.com/watch?v=mj4qodGsgDA