My problem is that I do not know how to extract the XML tag element using OpenXML. Any clarification would be great.
I have using xml.node before but now I am transitioning to OpenXML
My XML document can be found here
My Stored procedure:
ALTER procedure [dbo].[WEEKXml]
(@xmlstr ntext)
as
begin
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
insert into tbl_weekquake
select xml.time,xml.longitude,xml.latitude,xml.depth,xml.mag,xml.type,xml.description,xml.text
from OPENXML(@hDoc,'/q:quakeml/eventParameters/event',2)
with([time] varchar(300) 'times',
latitude numeric(18,6),
longitude numeric(18,6) ,
depth varchar(50),
mag varchar(50)'magnitude',
type varchar(50) 'mag_type',
description varchar(max) 'placer',
[text] varchar(50)'type')xml
exec sp_xml_removedocument @hDoc
END
One of your issues is that OPENXML does not handle "default namespace" very well. (besides the fact that no one recommends its usage anymore).
Here is how to force the use of a default namespace with openxml.
(from http://beyondrelational.com/modules/2/blogs/28/posts/10501/xquery-lab-59-openxml-and-xml-namespace-declarations.aspx )
declare @xmlDoc nvarchar(4000)
SET @xmlDoc='
<q:quakeml xmlns="http://quakeml.org/xmlns/bed/1.2" xmlns:catalog="http://anss.org/xmlns/catalog/0.1" xmlns:q="http://quakeml.org/xmlns/quakeml/1.2">
<eventParameters publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_hour.quakeml">
<event catalog:datasource="nc" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/detail/nc72431490.quakeml"><description><type>earthquake name</type><text>7km W of Cobb, California</text></description><origin catalog:datasource="nc" catalog:dataid="nc72431490" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml"><time><value>2015-04-20T13:08:32.490Z</value></time><longitude><value>-122.810997</value></longitude><latitude><value>38.8321648</value></latitude><depth><value>2340</value><uncertainty>940</uncertainty></depth><originUncertainty><horizontalUncertainty>380</horizontalUncertainty><preferredDescription>horizontal uncertainty</preferredDescription></originUncertainty><quality><usedPhaseCount>9</usedPhaseCount><usedStationCount>9</usedStationCount><standardError>0.02</standardError><azimuthalGap>114</azimuthalGap><minimumDistance>0.008787</minimumDistance></quality><evaluationMode>automatic</evaluationMode><creationInfo><agencyID>NC</agencyID><creationTime>2015-04-20T13:10:09.370Z</creationTime><version>0</version></creationInfo></origin><magnitude catalog:datasource="nc" catalog:dataid="nc72431490" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml#magnitude"><mag><value>0.29</value></mag><type>md</type><stationCount>1</stationCount><originID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml</originID><evaluationMode>automatic</evaluationMode><creationInfo><agencyID>NC</agencyID><creationTime>2015-04-20T13:10:09.370Z</creationTime></creationInfo></magnitude><preferredOriginID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml</preferredOriginID><preferredMagnitudeID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml#magnitude</preferredMagnitudeID><type>earthquake</type><creationInfo><agencyID>nc</agencyID><creationTime>2015-04-20T13:17:04.229Z</creationTime><version>0</version></creationInfo></event>
<creationInfo><creationTime>2015-04-20T13:31:33.000Z</creationTime></creationInfo>
</eventParameters></q:quakeml>
'
declare @i int
exec sp_xml_preparedocument @i output, @xmlDoc,'<root xmlns:HaveToUsePlaceholderForDefaultAlias="http://quakeml.org/xmlns/bed/1.2" xmlns:q="http://quakeml.org/xmlns/quakeml/1.2" />'
SELECT * FROM OPENXML(@i, '/q:quakeml/HaveToUsePlaceholderForDefaultAlias:eventParameters/HaveToUsePlaceholderForDefaultAlias:event',2)
WITH (
MyDescriptionType nvarchar(200) 'HaveToUsePlaceholderForDefaultAlias:description[1]/HaveToUsePlaceholderForDefaultAlias:type[1]/.'
, MyMagValue nvarchar(200) 'HaveToUsePlaceholderForDefaultAlias:magnitude[1]/HaveToUsePlaceholderForDefaultAlias:mag[1]/HaveToUsePlaceholderForDefaultAlias:value[1]/.'
, MagType nvarchar(200) 'HaveToUsePlaceholderForDefaultAlias:magnitude[1]/HaveToUsePlaceholderForDefaultAlias:type[1]/.'
)
exec sp_xml_removedocument @i
You have to riddle your code with "HaveToUsePlaceholderForDefaultAlias:" all over the place.
I would suggest NOT using OPENXML.
Here is the "nodes" version.......with a much cleaner use of the default namespace.
declare @xmldoc xml
select @xmldoc =
'
<q:quakeml xmlns="http://quakeml.org/xmlns/bed/1.2" xmlns:catalog="http://anss.org/xmlns/catalog/0.1" xmlns:q="http://quakeml.org/xmlns/quakeml/1.2">
<eventParameters publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_hour.quakeml">
<event catalog:datasource="nc" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/detail/nc72431490.quakeml">
<description>
<type>earthquake name</type>
<text>7km W of Cobb, California</text>
</description>
<origin catalog:datasource="nc" catalog:dataid="nc72431490" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml">
<time>
<value>2015-04-20T13:08:32.490Z</value>
</time>
<longitude>
<value>-122.810997</value>
</longitude>
<latitude>
<value>38.8321648</value>
</latitude>
<depth>
<value>2340</value>
<uncertainty>940</uncertainty>
</depth>
<originUncertainty>
<horizontalUncertainty>380</horizontalUncertainty>
<preferredDescription>horizontal uncertainty</preferredDescription>
</originUncertainty>
<quality>
<usedPhaseCount>9</usedPhaseCount>
<usedStationCount>9</usedStationCount>
<standardError>0.02</standardError>
<azimuthalGap>114</azimuthalGap>
<minimumDistance>0.008787</minimumDistance>
</quality>
<evaluationMode>automatic</evaluationMode>
<creationInfo>
<agencyID>NC</agencyID>
<creationTime>2015-04-20T13:10:09.370Z</creationTime>
<version>0</version>
</creationInfo>
</origin>
<magnitude catalog:datasource="nc" catalog:dataid="nc72431490" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml#magnitude">
<mag>
<value>0.29</value>
</mag>
<type>md</type>
<stationCount>1</stationCount>
<originID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml</originID>
<evaluationMode>automatic</evaluationMode>
<creationInfo>
<agencyID>NC</agencyID>
<creationTime>2015-04-20T13:10:09.370Z</creationTime>
</creationInfo>
</magnitude>
<preferredOriginID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml</preferredOriginID>
<preferredMagnitudeID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml#magnitude</preferredMagnitudeID>
<type>earthquake</type>
<creationInfo>
<agencyID>nc</agencyID>
<creationTime>2015-04-20T13:17:04.229Z</creationTime>
<version>0</version>
</creationInfo>
</event>
<creationInfo>
<creationTime>2015-04-20T13:31:33.000Z</creationTime>
</creationInfo>
</eventParameters>
</q:quakeml>
'
;WITH XMLNAMESPACES ( 'http://quakeml.org/xmlns/quakeml/1.2' AS q, DEFAULT 'http://quakeml.org/xmlns/bed/1.2' )
SELECT
MyDescriptionType = Y.i.value('./description[1]/type[1]/.', 'varchar(64)')
, MyMagValue = Y.i.value('./magnitude[1]/mag[1]/value[1]/.', 'varchar(64)')
, MagType = Y.i.value('./magnitude[1]/type[1]/.', 'varchar(64)')
FROM
@xmldoc.nodes('/q:quakeml/eventParameters/event') AS Y(i)
APPEND:
You might want to put the info in a #temp table, THEN insert the data from the #temp table to the "real" table.
IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
drop table #Holder
end
CREATE TABLE #Holder ( ID int identity (1000,1) , MyDescription varchar(64) , Longitude numeric(18,6) , Magnitude varchar(64) , MagType varchar(64) )
declare @xmldoc xml
select @xmldoc =
'
<q:quakeml xmlns="http://quakeml.org/xmlns/bed/1.2" xmlns:catalog="http://anss.org/xmlns/catalog/0.1" xmlns:q="http://quakeml.org/xmlns/quakeml/1.2">
<eventParameters publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_hour.quakeml">
<event catalog:datasource="nc" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/earthquakes/feed/v1.0/detail/nc72431490.quakeml">
<description>
<type>earthquake name</type>
<text>7km W of Cobb, California</text>
</description>
<origin catalog:datasource="nc" catalog:dataid="nc72431490" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml">
<time>
<value>2015-04-20T13:08:32.490Z</value>
</time>
<longitude>
<value>-122.810997</value>
</longitude>
<latitude>
<value>38.8321648</value>
</latitude>
<depth>
<value>2340</value>
<uncertainty>940</uncertainty>
</depth>
<originUncertainty>
<horizontalUncertainty>380</horizontalUncertainty>
<preferredDescription>horizontal uncertainty</preferredDescription>
</originUncertainty>
<quality>
<usedPhaseCount>9</usedPhaseCount>
<usedStationCount>9</usedStationCount>
<standardError>0.02</standardError>
<azimuthalGap>114</azimuthalGap>
<minimumDistance>0.008787</minimumDistance>
</quality>
<evaluationMode>automatic</evaluationMode>
<creationInfo>
<agencyID>NC</agencyID>
<creationTime>2015-04-20T13:10:09.370Z</creationTime>
<version>0</version>
</creationInfo>
</origin>
<magnitude catalog:datasource="nc" catalog:dataid="nc72431490" catalog:eventsource="nc" catalog:eventid="72431490" publicID="quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml#magnitude">
<mag>
<value>0.29</value>
</mag>
<type>md</type>
<stationCount>1</stationCount>
<originID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml</originID>
<evaluationMode>automatic</evaluationMode>
<creationInfo>
<agencyID>NC</agencyID>
<creationTime>2015-04-20T13:10:09.370Z</creationTime>
</creationInfo>
</magnitude>
<preferredOriginID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml</preferredOriginID>
<preferredMagnitudeID>quakeml:earthquake.usgs.gov/realtime/product/origin/nc72431490/nc/1429535409370/product.xml#magnitude</preferredMagnitudeID>
<type>earthquake</type>
<creationInfo>
<agencyID>nc</agencyID>
<creationTime>2015-04-20T13:17:04.229Z</creationTime>
<version>0</version>
</creationInfo>
</event>
<creationInfo>
<creationTime>2015-04-20T13:31:33.000Z</creationTime>
</creationInfo>
</eventParameters>
</q:quakeml>
'
;WITH XMLNAMESPACES ( 'http://quakeml.org/xmlns/quakeml/1.2' AS q, DEFAULT 'http://quakeml.org/xmlns/bed/1.2' )
INSERT INTO #HOLDER (MyDescription , Longitude , Magnitude , MagType )
SELECT
MyDescriptionType = Y.i.value('./description[1]/type[1]/.', 'varchar(64)')
, MyLongitude = Y.i.value('./origin[1]/longitude[1]/.', 'numeric(18,6)')
, MyMagValue = Y.i.value('./magnitude[1]/mag[1]/value[1]/.', 'varchar(64)')
, MagType = Y.i.value('./magnitude[1]/type[1]/.', 'varchar(64)')
FROM
@xmldoc.nodes('/q:quakeml/eventParameters/event') AS Y(i)
/* Optional */
/* CREATE CLUSTERED INDEX IDX_TempHolder_ID ON #Holder (ID) */
CREATE INDEX IDX_TempHolder_ID ON #Holder (ID)
/* HERE you go from #temp table to real table */
Insert into dbo.RealTable ( Description , Longitude , Magnitude , MagType )
select MyDescription , Longitude , Magnitude , MagType from #Holder
IF OBJECT_ID('tempdb..#Holder') IS NOT NULL
begin
drop table #Holder
end
For raw text of loaded document in WordProcessingDocument, you can use.
string rawText = wordprocessingDocument.MainDocumentPart.Document.ChildElements[0].InnerText;
For XML:
string rawText = wordprocessingDocument.MainDocumentPart.Document.ChildElements[0].InnerXml;
