johnnyg123
asked on
xquery question
I am running the following in sql server 2008 query analyzer ....(I am a newbie with xquery in sql)
declare @xml xml
set @xml =
'<dataextract>
<organizations>
<organization id="128313" name="GVC" type="Unit" />
</organizations>
<questions>
<question id="360" text="Info: What Prompted Your Visit?" />
</questions>
<participants>
<participant id="D1CE7DB6CB261B815CD1D3 F21BB8E252 " orgid="128313">
<attributes>
<attribute name="Organization">GV</at tribute>
<importedattribute name="First Name" id="FirstName" datatype="Char">VERINO</im portedattr ibute>
<importedattribute name="Last Name" id="LastName" datatype="Char">MESSERE</i mportedatt ribute>
<importedattribute name="Email Address" id="EmailAddress" datatype="Char">golfvj@tes t.com</imp ortedattri bute>
<importedattribute name="Date In" id="ServiceStartDate" datatype="Date">2014-08-10 T00:00:00< /importeda ttribute>
<importedattribute name="Loyalty ID" id="LoyaltyID" datatype="Char">7000482</i mportedatt ribute>
<importedattribute name="Loyalty level" id="LoyaltyLevel" datatype="Char">3</importe dattribute >
<attribute name="Sent Date">2014-08-12T14:15:59. 403</attri bute>
<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</r esponse>
<response questionid="10306" orgid="128352">50</respons e>
</responses>
</participant>
</participants>
</dataextract>'
select
tab.col.value('(@LoyaltyLe vel)[1]', 'nvarchar(100)') LoyaltyLevel,
tab1.col1.value('(@questio nid)[1]', 'nvarchar(100)') questionid,
tab1.col1.value('(@orgid)[ 1]', 'nvarchar(100)') orgId,
tab1.col1.value('.', 'nvarchar(100)') response
from @xml.nodes('/dataextract/p articipant s/particip ant/respon ses') 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
declare @xml xml
set @xml =
'<dataextract>
<organizations>
<organization id="128313" name="GVC" type="Unit" />
</organizations>
<questions>
<question id="360" text="Info: What Prompted Your Visit?" />
</questions>
<participants>
<participant id="D1CE7DB6CB261B815CD1D3
<attributes>
<attribute name="Organization">GV</at
<importedattribute name="First Name" id="FirstName" datatype="Char">VERINO</im
<importedattribute name="Last Name" id="LastName" datatype="Char">MESSERE</i
<importedattribute name="Email Address" id="EmailAddress" datatype="Char">golfvj@tes
<importedattribute name="Date In" id="ServiceStartDate" datatype="Date">2014-08-10
<importedattribute name="Loyalty ID" id="LoyaltyID" datatype="Char">7000482</i
<importedattribute name="Loyalty level" id="LoyaltyLevel" datatype="Char">3</importe
<attribute name="Sent Date">2014-08-12T14:15:59.
<attribute name="Submitted Date">2014-08-13T00:00:00<
<attribute name="Survey Score">61.046511627906973<
</attributes>
<responses>
<response questionid="360" orgid="128313">Pleasure</r
<response questionid="10306" orgid="128352">50</respons
</responses>
</participant>
</participants>
</dataextract>'
select
tab.col.value('(@LoyaltyLe
tab1.col1.value('(@questio
tab1.col1.value('(@orgid)[
tab1.col1.value('.', 'nvarchar(100)') response
from @xml.nodes('/dataextract/p
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you tried using OPENXML()
Alternatively, you may want to consider importing using SSIS.
Alternatively, you may want to consider importing using SSIS.
ASKER