Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
276 views
Welcome To Ask or Share your Answers For Others

1 Answer

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...