sql server - Extract XML using OPENXML()

Question

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

Answer 1

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</