openxml

Trying to run the following in sql server 2008

DECLARE @idoc 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 @idoc OUTPUT, @doc;
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT    *
FROM       OPENXML (@idoc, 'dataextract/responses/response',2)
            WITH (questionid  varchar(10),
                  orgid varchar(20),
                   response varchar(1000));

I was hoping results would be

questionID     orgid        response
360                  128313    Pleasure

unfortunately, I am getting null

Can someone please tell me what I'm missing
johnnyg123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
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" />
 

Open 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))

Open in new window

0
johnnyg123Author Commented:
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
0
jogosCommented:
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';

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

jogosCommented:
But your your filter is from other level so you must work your way back

with cte  as(SELECT    *
FROM       OPENXML (@hdoc, '/dataextract/dataextract/participants/participant/responses/response/@questionid')
            WITH (questionid  varchar(10) '.' ,
                  orgid varchar(20) '../@orgid',
                   response ntext '../text()'
                 ,attrname varchar(20) '../../../attributes/importedattribute/@name'
                 ,attrId varchar(20) '../../../attributes/importedattribute/@id'
                 ,attrValue varchar(20) '../../../attributes/importedattribute/text()'
                 )
           )
select * from cte

Open in new window

Or

with cte  as(SELECT    *
FROM       OPENXML (@hdoc, '/dataextract/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'

Open in new window


In this version it works with your example, but you will see it does not give all nodes.

I think Xquery will be more suited for filtering this
http://msdn.microsoft.com/en-us/library/ms189075(v=sql.105).aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
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
0
johnnyg123Author Commented:
I think you are correct that xquery may be more beneficial

I will close this question and open one for xquery question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.