Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


xquery question

Posted on 2014-08-26
Medium Priority
Last Modified: 2014-09-03
I am running the following in sql server 2008 query analyzer ....(I am a newbie with xquery in sql)

declare @xml xml
       set @xml =
                  <organization id="128313" name="GVC" type="Unit" />
                  <question id="360" text="Info: What Prompted Your Visit?" />
                  <participant id="D1CE7DB6CB261B815CD1D3F21BB8E252" orgid="128313">
                        <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>
                        <response questionid="360" orgid="128313">Pleasure</response>
                        <response questionid="10306" orgid="128352">50</response>


                   tab.col.value('(@LoyaltyLevel)[1]', 'nvarchar(100)') LoyaltyLevel,
             tab1.col1.value('(@questionid)[1]', 'nvarchar(100)') questionid,
             tab1.col1.value('(@orgid)[1]', 'nvarchar(100)') orgId,
                   tab1.col1.value('.', 'nvarchar(100)') response
       from @xml.nodes('/dataextract/participants/participant/responses') as tab(col)
         cross apply col.nodes('response') as tab1(col1)

I am getting

LoyaltyLevel      questionid      orgId      response
NULL              360                     128313      Pleasure
NULL              10306             128352      50

I would like it to make changes so that it includes the loyalty id that the question is associated with

so that the results are

LoyaltyLevel      questionid      orgId      response
7000482              360                     128313      Pleasure
7000482              10306             128352      50
Question by:johnnyg123
  • 2
LVL 75

Accepted Solution

Anthony Perkins earned 2000 total points
ID: 40287029
I am not sure if you mean Loyalty Level or Loyalty ID.  The values you post belong to Loyalty ID and not Loyalty Level.
But try this and pick which one you want:
 SELECT tab.col.value('(../../attributes/importedattribute[@name="Loyalty ID"]/.)[1]', 'nvarchar(100)') [Loyalty ID],
		tab.col.value('(../../attributes/importedattribute[@name="Loyalty level"]/.)[1]', 'nvarchar(100)') [Loyalty level],
        tab.col.value('@questionid', 'nvarchar(100)') questionid,
        tab.col.value('@orgid', 'nvarchar(100)') orgId,
        tab.col.value('.', 'nvarchar(100)') response
 FROM   @xml.nodes('/dataextract/participants/participant/responses/response') AS tab (col)

Open in new window

Here is the output:
Loyalty ID      Loyalty level      questionid      orgid      response
7000482      3      360      128313      Pleasure
7000482      3      10306      128352      50

Author Comment

ID: 40287799
It works perfectly...only thing is that when I run it against my entire file which has like 100000 rows.  It is taking like an hour to finish.  Any way to speed it up???
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40289405
Have you tried using OPENXML()

Alternatively, you may want to consider importing using SSIS.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
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. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

579 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