Databases
StackOverflow2013
Postsdbo
POQuerying a document in SQL Server 2012 stored as xml
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POQuerying a document in SQL Server 2012 stored as xml
    text
    copied!<p>I have been trying to learn Open XML, and I successfully generated a free text 2007 .docx file in Visual Studio c#. After that I saved the file in the .xml format in Word and then Imported the .xml file into sql server 2012 into a table called XML to a column called xml: here is the code for the table:</p> <pre><code>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 </code></pre> <p>Here is the insert I ran</p> <pre><code>INSERT INTO XML(XML) SELECT * FROM OPENROWSET( BULK 'C:\Users\bbt2d\Desktop\Document.xml', SINGLE_BLOB) AS x; </code></pre> <p>When I ran a select * the xml file showed up, but when I tried querying some of the data from the paragraphs I wasn't getting anywhere.</p> <p>Here is a partial snipper of the xml that is stored in sql server: Was wondering if I went about the process wrong, or how would you query out text from the document.</p> <pre><code>?mso-application progid="Word.Document"?&gt; &lt;pkg:package xmlns:pkg="http://schemas.microsoft.com/office/2006/xmlPackage"&gt; &lt;pkg:part pkg:name="/_rels/.rels" pkg:contentType="application/vnd.openxmlformats-package.relationships+xml" pkg:padding="512"&gt; &lt;pkg:xmlData&gt; &lt;Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"&gt; &lt;Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml" /&gt; &lt;Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml" /&gt; &lt;Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="word/document.xml" /&gt; &lt;/Relationships&gt; &lt;/pkg:xmlData&gt; &lt;/pkg:part&gt; &lt;pkg:part pkg:name="/word/_rels/document.xml.rels" pkg:contentType="application/vnd.openxmlformats-package.relationships+xml" pkg:padding="256"&gt; &lt;pkg:xmlData&gt; &lt;Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"&gt; &lt;Relationship Id="rId8" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/fontTable" Target="fontTable.xml" /&gt; &lt;Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/settings" Target="settings.xml" /&gt; &lt;Relationship Id="rId7" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/footer" Target="footer1.xml" /&gt; &lt;Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml" /&gt; &lt;Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/numbering" Target="numbering.xml" /&gt; &lt;Relationship Id="rId6" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/endnotes" Target="endnotes.xml" /&gt; &lt;Relationship Id="rId5" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/footnotes" Target="footnotes.xml" /&gt; &lt;Relationship Id="rId10" Type="http://schemas.microsoft.com/office/2007/relationships/stylesWithEffects" Target="stylesWithEffects.xml" /&gt; &lt;Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/webSettings" Target="webSettings.xml" /&gt; &lt;Relationship Id="rId9" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml" /&gt; &lt;/Relationships&gt; &lt;/pkg:xmlData&gt; &lt;/pkg:part&gt; &lt;pkg:part pkg:name="/word/document.xml" pkg:contentType="application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml"&gt; &lt;pkg:xmlData&gt; &lt;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"&gt; &lt;w:body&gt; &lt;w:p w:rsidR="00C32D34" w:rsidRDefault="00C32D34"&gt; &lt;w:pPr&gt; &lt;w:pStyle w:val="Heading1" /&gt; &lt;w:spacing w:line="240" w:lineRule="auto" /&gt; &lt;w:jc w:val="center" /&gt; &lt;w:rPr&gt; &lt;w:rFonts w:ascii="Times New Roman" w:hAnsi="Times New Roman" /&gt; &lt;w:sz w:val="44" /&gt; &lt;w:szCs w:val="44" /&gt; &lt;/w:rPr&gt; &lt;/w:pPr&gt; &lt;w:r&gt; &lt;w:rPr&gt; &lt;w:rFonts w:ascii="Times New Roman" w:hAnsi="Times New Roman" /&gt; &lt;w:sz w:val="44" /&gt; &lt;w:szCs w:val="44" /&gt; &lt;/w:rPr&gt; &lt;w:t&gt;School District of Marshfield&lt;/w:t&gt; &lt;/w:r&gt; &lt;/w:p&gt; &lt;w:p w:rsidR="00C32D34" w:rsidRDefault="00C32D34"&gt; &lt;w:pPr&gt; &lt;w:autoSpaceDE w:val="0" /&gt; &lt;w:autoSpaceDN w:val="0" /&gt; &lt;w:adjustRightInd w:val="0" /&gt; &lt;w:spacing w:after="0" w:line="240" w:lineRule="auto" /&gt; &lt;w:rPr&gt; &lt;w:rFonts w:ascii="Cambria" w:hAnsi="Cambria" w:cs="Cambria" /&gt; &lt;w:b /&gt; &lt;w:bCs /&gt; &lt;w:sz w:val="28" /&gt; &lt;w:szCs w:val="28" /&gt; &lt;/w:rPr&gt; &lt;/w:pPr&gt; &lt;/w:p&gt; &lt;w:p w:rsidR="00C32D34" w:rsidRDefault="00DE219B"&gt; &lt;w:pPr&gt; &lt;w:autoSpaceDE w:val="0" /&gt; &lt;w:autoSpaceDN w:val="0" /&gt; &lt;w:adjustRightInd w:val="0" /&gt; &lt;w:spacing w:after="0" w:line="240" w:lineRule="auto" /&gt; &lt;w:jc w:val="center" /&gt; &lt;w:rPr&gt; &lt;w:rFonts w:ascii="Cambria" w:hAnsi="Cambria" w:cs="Cambria" /&gt; &lt;w:b /&gt; &lt;w:bCs /&gt; &lt;w:sz w:val="28" /&gt; &lt;w:szCs w:val="28" /&gt; &lt;/w:rPr&gt; &lt;/w:pPr&gt; &lt;w:r&gt; &lt;w:rPr&gt; &lt;w:rFonts w:ascii="Cambria" w:hAnsi="Cambria" w:cs="Cambria" /&gt; &lt;w:b /&gt; &lt;w:bCs /&gt; &lt;w:sz w:val="28" /&gt; &lt;w:szCs w:val="28" /&gt; &lt;/w:rPr&gt; &lt;w:t&gt;Exploring French&lt;/w:t&gt; &lt;/w:r&gt; &lt;w:r w:rsidR="00C32D34"&gt; &lt;w:rPr&gt; &lt;w:rFonts w:ascii="Cambria" w:hAnsi="Cambria" w:cs="Cambria" /&gt; &lt;w:b /&gt; &lt;w:bCs /&gt; &lt;w:sz w:val="28" /&gt; &lt;w:szCs w:val="28" /&gt; &lt;/w:rPr&gt; &lt;w:t xml:space="preserve"&gt; &amp;amp; Spanish&lt;/w:t&gt; &lt;/w:r&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload