openxml

Do you know there is al nested tag  <dataextract>?

  <dataextract>
  <runparameters>Daily Data Extract - 2014-08-13 : 2014-08-13</runparameters>
  <dataextract>
    <organizations>
      <organization id="128313" name="GVC" type="Unit" />
 

Select allOpen in new window

and that you miss some levels

SELECT    *
FROM       OPENXML (@idoc, '/dataextracts/dataextract/participants/participant/responses/response',2)
            WITH (questionid  varchar(10),
                  orgid varchar(20),
                   response varchar(1000))

Select allOpen in new window

I appreciate the answer

I apologize but I am a total xml newbie


 I got my desired result


 questionID     orgid        response
 360                  128313    Pleasure

using




DECLARE @hdoc int, @doc varchar(max);

 SET @doc ='

   <dataextract>
   <runparameters>Daily Data Extract - 2014-08-13 : 2014-08-13</runparameters>
   <dataextract>
     <organizations>
       <organization id="128313" name="GVC" type="Unit" />
     </organizations>
     <questions>
       <question id="360" text="Info: What Prompted Your Visit?" />
     </questions>
     <participants>
       <participant id="D1CE7DB6CB261B815CD1D3F21BB8E252" orgid="128313">
         <attributes>
           <attribute name="Organization">GV</attribute>
           <importedattribute name="First Name" id="FirstName" datatype="Char">VERINO</importedattribute>
           <importedattribute name="Last Name" id="LastName" datatype="Char">MESSERE</importedattribute>
           <importedattribute name="Email Address" id="EmailAddress" datatype="Char">golfvj@test.com</importedattribute>
           <importedattribute name="Date In" id="ServiceStartDate" datatype="Date">2014-08-10T00:00:00</importedattribute>
           <importedattribute name="Loyalty ID" id="LoyaltyID" datatype="Char">7000482</importedattribute>
           <importedattribute name="Loyalty level" id="LoyaltyLevel" datatype="Char">3</importedattribute>
           <attribute name="Sent Date">2014-08-12T14:15:59.403</attribute>
           <attribute name="Submitted Date">2014-08-13T00:00:00</attribute>
           <attribute name="Survey Score">61.046511627906973</attribute>
         </attributes>
         <responses>
           <response questionid="360" orgid="128313">Pleasure</response>
            </responses>
       </participant>
      </participants>
   </dataextract>
 </dataextract>
 ';
 --Create an internal representation of the XML document.
 EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc;
 -- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@hdoc, '/dataextract/dataextract/participants/participant/responses/response',1)
            WITH (questionid  varchar(10),
                  orgid varchar(20),
                   response ntext 'text()')
                         

EXEC sp_xml_removedocument @hDoc

The one thing I forgot to mention is that I need to tie the question response to the participant via the  Loyalty ID which in the example is 7000482

So I need the result to be

LoyaltyId     questionID     orgid        response
 7000482     360                  128313    Pleasure

Not sure how to do this

Use a cte for selecting and do the filtering on the result

;with cteXml as(SELECT    *
FROM       OPENXML (@hdoc, '/dataextract/dataextract/participants/participant/responses/response',1)
            WITH (questionid  varchar(10),
                  orgid varchar(20),
                   response ntext 'text()')
           )
select * from cteXML where orgid = '128313';

Select allOpen in new window

Can you tell me when I run


DECLARE @hdoc int, @doc varchar(max);

 SET @doc ='


      <dataextract>
            <organizations>
                  <organization id="128313" name="GVC" type="Unit" />
            </organizations>
            <questions>
                  <question id="360" text="Info: What Prompted Your Visit?" />
            </questions>
            <participants>
                  <participant id="D1CE7DB6CB261B815CD1D3F21BB8E252" orgid="128313">
                  <attributes>
                        <attribute name="Organization">GV</attribute>
                              <importedattribute name="First Name" id="FirstName" datatype="Char">VERINO</importedattribute>
                              <importedattribute name="Last Name" id="LastName" datatype="Char">MESSERE</importedattribute>
                              <importedattribute name="Email Address" id="EmailAddress" datatype="Char">golfvj@test.com</importedattribute>
                              <importedattribute name="Date In" id="ServiceStartDate" datatype="Date">2014-08-10T00:00:00</importedattribute>
                              <importedattribute name="Loyalty ID" id="LoyaltyID" datatype="Char">7000482</importedattribute>
                              <importedattribute name="Loyalty level" id="LoyaltyLevel" datatype="Char">3</importedattribute>
                              <attribute name="Sent Date">2014-08-12T14:15:59.403</attribute>
                              <attribute name="Submitted Date">2014-08-13T00:00:00</attribute>
                              <attribute name="Survey Score">61.046511627906973</attribute>
                        </attributes>
                  <responses>
                        <response questionid="360" orgid="128313">Pleasure</response>
                        <response questionid="10306" orgid="128352">50</response>
                  </responses>
                  </participant>
                  </participants>
                  </dataextract>
                  
 ';
 --Create an internal representation of the XML document.
 EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc;
 -- Execute a SELECT statement that uses the OPENXML rowset provider.
with cte  as(SELECT    *
FROM       OPENXML (@hdoc, '/dataextract/participants/participant/attributes/importedattribute/@name')
            WITH (questionid  varchar(10) '../../../responses/response/@questionid' ,
                  orgid varchar(20) '../../../responses/response/@orgid',
                   response ntext '../../../responses/response/text()'
                 ,attrname varchar(20) '.'
                 ,attrId varchar(20) '../@id'
                 ,attrValue varchar(20) '../text()'
                 )
           )
select * from cte where attrid = 'LoyaltyID'
and attrValue = '7000482'
EXEC sp_xml_removedocument @hDoc

why I only get the first question id

questionid      orgid      response      attrname      attrId              attrValue
360                      128313      Pleasure              Loyalty ID      LoyaltyID      7000482


I was hoping to get
questionid      orgid      response      attrname      attrId              attrValue
360                      128313      Pleasure              Loyalty ID      LoyaltyID      7000482
10306             128352      50                     Loyalty ID      LoyaltyID      7000482

I think you are correct that xquery may be more beneficial

I will close this question and open one for xquery question