When working with XML in SQL Server you use the xml Data Type Methods and when shredding XML documents you typicly use the nodes()
and value()
methods. The XML you have here also include a number of namespaces so you have to specify the ones you need using WITH XMLNAMESPACES (Transact-SQL).
The XML is quite complex so without knowing how you want the data extracted I can only give you a couple of sample queries that you then can modify to whatever it is you need.
You have four w:p
nodes and here is a query that fetches the attributes from those nodes. Using @
specifies that it is the value of an attribute you want-
with xmlnamespaces('http://schemas.openxmlformats.org/wordprocessingml/2006/main' as w)
select P.X.value('@w:rsidR', 'char(8)') as rsidR,
P.X.value('@w:rsidRDefault', 'char(8)') as rsidRDefault,
P.X.value('@w:rsidP', 'char(8)') as rsidP
from @doc.nodes('/w:document/w:body/w:p') as P(X);
SQL Fiddle
If you in addition to that want the text in the w:t
node you need to do a cross apply
to a second nodes()
clause that will shred the XML within the w:p
node.
with xmlnamespaces('http://schemas.openxmlformats.org/wordprocessingml/2006/main' as w)
select P.X.value('@w:rsidR', 'char(8)') as rsidR,
P.X.value('@w:rsidRDefault', 'char(8)') as rsidRDefault,
P.X.value('@w:rsidP', 'char(8)') as rsidP,
T.X.value('text()[1]', 'nvarchar(max)') as Text
from @doc.nodes('/w:document/w:body/w:p') as P(X)
cross apply P.X.nodes('w:r/w:t') as T(X);
SQL Fiddle
You said in your question that you want to get the values from all the tags. I don't know how useful this is to you but you can build a Name-Value list with all the attributes and elements in the XML.
This will give you all elements.
select T.X.value('local-name(.)', 'nvarchar(max)') Name,
T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//*') as T(X)
Change '//*'
to '//@*'
and you will get all attributes.
select T.X.value('local-name(.)', 'nvarchar(max)') Name,
T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//@*') as T(X)
And you can combine them in one query as well.
select T.X.value('local-name(.)', 'nvarchar(max)') Name,
T.X.value('.', 'nvarchar(max)') Value
from @doc.nodes('//@*, //*') as T(X)
SQL Fiddle
You can use X-Query for that:
SQL Fiddle
MS SQL Server 2008 Schema Setup:
CREATE PROCEDURE dbo.ParseXML
@XML XML
AS
BEGIN
SELECT
Book.value('@id','NVARCHAR(100)') AS id,
Book.value('author[1]','NVARCHAR(100)') AS author,
Book.value('title[1]','NVARCHAR(100)') AS title,
Book.value('price[1]','DECIMAL(10,4)') AS price,
Book.value('publish_date[1]','DATE') AS publish_date
FROM @XML.nodes('catalog/book')Catalog(Book)
RETURN;
END;
Query 1:
DECLARE @FileXML XML = '<?xml version="1.0"?>
<catalog>
<book id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer''s Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
<description>An in-depth look at creating applications
with XML.</description>
</book>
<book id="bk102">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-12-16</publish_date>
<description>A former architect battles corporate zombies,
an evil sorceress, and her own childhood to become queen
of the world.</description>
</book>
</catalog>';
EXEC dbo.ParseXML @FileXML;
Results:
| ID | AUTHOR | TITLE | PRICE | PUBLISH_DATE |
|-------|----------------------|-----------------------|-------|--------------|
| bk101 | Gambardella, Matthew | XML Developer's Guide | 44.95 | 2000-10-01 |
| bk102 | Ralls, Kim | Midnight Rain | 5.95 | 2000-12-16 |
This shows you how to get to the values. From there you can easily insert them into your table.
Best Answer
Use the XQuery CONTAINS method, like this:
Good luck.