?
Solved

openxml

Posted on 2014-08-21
6
Medium Priority
?
183 Views
Last Modified: 2014-08-26
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
0
Comment
Question by:johnnyg123
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 40278480
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
 

Author Comment

by:johnnyg123
ID: 40279015
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
 
LVL 25

Expert Comment

by:jogos
ID: 40279539
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 25

Accepted Solution

by:
jogos earned 2000 total points
ID: 40279667
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
 

Author Comment

by:johnnyg123
ID: 40284395
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
 

Author Closing Comment

by:johnnyg123
ID: 40286695
I think you are correct that xquery may be more beneficial

I will close this question and open one for xquery question
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question