xquery question

Posted on 2014-08-26
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"></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
    LVL 75

    Accepted Solution

    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

    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
    Have you tried using OPENXML()

    Alternatively, you may want to consider importing using SSIS.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now