Import xml to sql

Here is my situation

I am receiving an xml file with survey data results on a daily basis.  The survey has about 20 questions

I need to import this into a sql table so I can then load it into our data warehouse

I have really never worked with xml before so I did a bit of research and found the example below


CREATE PROCEDURE dbo.my_XML_update
      @XMLData NTEXT,
      @myId SMALLINT
AS
SET NOCOUNT ON

-- Retrieve XML data
DECLARE @hocDoc INT

DECLARE @Comments NVARCHAR(2048)

EXEC SP_XML_PREPAREDOCUMENT @hocDoc OUTPUT, @XMLData

-- OPEN XML
-- Param1 is Pointer to DOC
-- Param2 is XML doc hiearchy
-- Param3 is Use Element Structure rather than Attributes


SELECT
      ContactId,
      ContactName
INTO
      #tmpContact
FROM
      OPENXML(@hocDoc, '/root/Contact', 2)
WITH (ContactId  INTEGER, ContactName NVARCHAR(50))

--this is how you populate the contact xml
SELECT Contact.ContactId,
            ISNULL(Contact.ContactName, '') AS ContactName
FROM #tmpContact Contact, Contact C
WHERE      Contact.ContactId = C.Id
ORDER BY      Contact.ContactName
FOR XML AUTO, ELEMENTS

-- Then you can do an INSERT
INSERT INTO Contact (Id, ContactName)
      SELECT ContactId,ContactName
      FROM #tmpContact

--or an UPDATE
UPDATE Contact SET ContactName = t.ContactName
FROM #tmpContact t
                  WHERE      contact.Id = #tmpContact.ContactId
           
-- Drop the temp Table
DROP TABLE #tmpContact

-- and MUST release XML doc to avoid memeory issues
EXEC SP_XML_REMOVEDOCUMENT @hocDoc
GO

This is very helpful except it seems to assume that the xml file will contain an entry for all fields the table

I am being told that if a survey participant did not answer a question.  It will not be included in the xml file

Not sure how to handle this situation in which some data fields may not have an entry
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.

lcohanDatabase AnalystCommented:
Nice xml import code - I believe that's mine btw and regardless...In my opinion you maybe confusing XML nodes rows with table columns - fields as you call them. Let's just think if you have a survey like matrix those columns(fields) would be all represented in the XML therefor in the table even though some user may be able to skip some question(s) right? BTW - all surveys I created or completed on-line or not have a pretty fixed matrix structure and not too many optional to skip a question therefor leaving a empty node/line right? even if you don't want to answer you state that or select some "Other" option.
Hope this makes sense and helps...Also, please have a look at how you "can create a table to match the xml from the xml file" at:

http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28301844.html
0
johnnyg123Author Commented:
I was wondering if you would respond...very nice code indeed :-)


I get the file from our marketing vendor who claims that have other customers who have no problem with unanswered questions not being included

For example:

Here is list of questions

<questions>
<question id="88" text="General Comments"/>
<question id="360" text="Info: What Prompted Your Visit?"/>
<question id="372" text="PrvLounge: Use of Name?"/>
<question id="748" text="Staff: Exceptional Service Name"/>
<question id="750" text="Staff: Exceptional Service? "/>
<question id="906" text="Loyalty: Return?"/>
<question id="907" text="Loyalty: Recommend?"/>
<question id="968" text="Rest: Comments"/>
<question id="1798" text="Emotions: Excited low rating explained"/>
<question id="1939" text="Info: Primary Reason Other"/>
</questions>

This respondent only answered question id 360, 750, 906 and 907
(No entries for other question id's)

<participants><participant id="31348B6E304BA95033F3B75DC755917B" orgid="128313"><attributes><attribute name="Organization">
<![CDATA[GVC]]>
</attribute><importedattribute name="First Name" id="FirstName" datatype="Char">
<![CDATA[TODD]]>
</importedattribute><importedattribute name="Last Name" id="LastName" datatype="Char">
<![CDATA[PLETT]]>
</importedattribute><importedattribute name="Email Address" id="EmailAddress" datatype="Char">
<![CDATA[toddplett39@gmail.com]]>
</importedattribute><importedattribute name="Date In" id="ServiceStartDate" datatype="Date">2014-08-10T00:00:00</importedattribute><importedattribute name="Loyalty ID" id="LoyaltyID" datatype="Char">
<![CDATA[7357792]]>
</importedattribute><importedattribute name="Loyalty level" id="LoyaltyLevel" datatype="Char">
<![CDATA[3]]>
</importedattribute><attribute name="Sent Date">2014-08-12T14:17:14.2</attribute><attribute name="Submitted Date">2014-08-19T00:00:00</attribute><attribute name="Survey Score">93.75</attribute></attributes>
<responses><response orgid="128313" questionid="360">
<![CDATA[Pleasure]]>
</response><response orgid="128313" questionid="750">
<![CDATA[No]]>
</response><response orgid="128313" questionid="906">
<![CDATA[Very likely]]>
</response><response orgid="128313" questionid="907">
<![CDATA[Very likely]]>
</response>
</responses></participant>
0
johnnyg123Author Commented:
I tried this to get list of questions but get no data

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

SELECT @XML = XMLData FROM XMLwithOpenXML

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT QuestionID, Text
FROM OPENXML(@hDoc, 'dataextract/dataextract/questions')
WITH
(
QuestionID [int] '@QuestionID',
Text [varchar](100) '@Text'
)

EXEC sp_xml_removedocument @hDoc

if xml file looks like this

<?xml version="1.0" encoding="ISO-8859-1"?>

-<dataextract>


-<runparameters>

<![CDATA[GVC - 2014-08-19 : 2014-08-19]]>

</runparameters>


-<dataextract>


-<organizations>

<organization type="Unit" name="GVC" id="128313"/>

<organization type="Outlet" name="Steakhouse" id="128349"/>

<organization type="Outlet" name="Burgerhouse" id="128350"/>

<organization type="Outlet" name="Deli" id="128351"/>

<organization type="Outlet" name="Buffet" id="128352"/>

</organizations>


-<questions>

<question id="88" text="General Comments"/>

<question id="112" text="Entertnmt: Comments"/>

<question id="264" text="PrvLounge: Comments"/>

<question id="332" text="PrvLounge: Entrance Greeting?"/>

<question id="360" text="Info: What Prompted Your Visit?"/>

<question id="372" text="PrvLounge: Use of Name?"/>

<question id="748" text="Staff: Exceptional Service Name"/>

<question id="750" text="Staff: Exceptional Service? "/>

<question id="906" text="Loyalty: Return?"/>

<question id="907" text="Loyalty: Recommend?"/>


</questions>


</dataextract>

</dataextract>

what am I doing wrong?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Anthony PerkinsCommented:
That Xml is not well-formed.  It does not have a top level element and dataextract does not have a closing tag.
0
johnnyg123Author Commented:
sorry.... I was trying to cut down for space sake  ... apparently my copy and paste didn't work out

I ran this through a couple of xml validators and it passed the test


DECLARE @hdoc int, @doc varchar(max);

 SET @doc ='
 
      <?xml version="1.0" encoding="iso-8859-1"?><dataextract>
  <runparameters><![CDATA[GVC - GVC Daily Data Extract - 2014-08-19 : 2014-08-19]]></runparameters>
  <dataextract>
    <organizations>
      <organization id="128313" name="GVC" type="Unit" />
      <organization id="128349" name="Steakhouse" type="Outlet" />
      <organization id="128350" name="Burgerhouse" type="Outlet" />
      <organization id="128351" name="Deli" type="Outlet" />
      <organization id="128352" name="Buffet" type="Outlet" />
      <organization id="128353" name="Lounge" type="Outlet" />
      <organization id="128354" name="Bar" type="Outlet" />
    </organizations>
    <questions>
      <question id="88" text="General Comments" />
      <question id="108" text="Slots: Comments" />
      <question id="109" text="Tables: Comments" />
      <question id="110" text="Poker: Comments" />
      <question id="112" text="Entertnmt: Comments" />
      <question id="264" text="PrvLounge: Comments" />
      <question id="332" text="PrvLounge: Entrance Greeting?" />
      <question id="360" text="Info: What Prompted Your Visit?" />
      <question id="372" text="PrvLounge: Use of Name?" />
      <question id="748" text="Staff: Exceptional Service Name" />
      <question id="750" text="Staff: Exceptional Service? " />
      <question id="906" text="Loyalty: Return?" />
      <question id="907" text="Loyalty: Recommend?" />
      <question id="968" text="Rest: Comments" />
      <question id="1243" text="Cage: Comments  " />
      <question id="1244" text="Club GVC: Comments" />
      <question id="1414" text="Lounge: Comments" />
      <question id="1783" text="Casino: Comments" />
      <question id="1798" text="Emotions: Excited low rating explained" />
      <question id="1939" text="Info: Primary Reason Other" />
      <question id="2037" text="Tables: Game Variety" />
      <question id="2292" text="Buffet: Value" />
      <question id="2303" text="Rest: Service Quality" />
      <question id="2304" text="Rest: Atmosphere" />
      <question id="2305" text="Casino: Security Accessibility" />
      <question id="2326" text="Slots: Area Cleanliness" />
      <question id="2339" text="Emotions: Entertained" />
      <question id="3074" text="Emotions: Important" />
      <question id="3125" text="Rest: Area Cleanliness" />
      <question id="3127" text="Rest: Value" />
      <question id="3265" text="Cage: Wait Time " />
      <question id="3266" text="Cage: ATM Availability " />
      <question id="3268" text="Casino: Cleanliness" />
      <question id="3269" text="Tables: Bev Serv Timeliness" />
      <question id="3271" text="Casino: Temperature" />
      <question id="3272" text="Casino: Ventilation" />
      <question id="3273" text="Entertnmt: Quality" />
      <question id="3274" text="Casino: Experience" />
      <question id="3279" text="Valet: Team Friendliness" />
      <question id="3280" text="Valet: Services Timeliness" />
      <question id="3290" text="Rest: Service Timeliness" />
      <question id="3291" text="Rest: Staff Friendliness" />
      <question id="3293" text="Buffet: Area Cleanliness" />
      <question id="3306" text="Slots: Game Variety" />
      <question id="3307" text="Slots: Machine Availability " />
      <question id="3308" text="Slots: Attendant Friendliness" />
      <question id="3313" text="Tables: Dealer Friendliness" />
      <question id="3314" text="Poker: Limits Variety" />
      <question id="3316" text="Club GVC: Representatives Friendliness" />
      <question id="3317" text="Club GVC: Wait Time" />
      <question id="3319" text="Club GVC: Program Value" />
      <question id="3369" text="Lounge: Service Timeliness" />
      <question id="3370" text="Lounge: Staff Friendliness" />
      <question id="3371" text="PrvLounge: Area Cleanliness" />
      <question id="3832" text="Slots: FB Quality" />
      <question id="3855" text="Buffet: Staff Friendliness" />
      <question id="3864" text="Emotions: Welcome" />
      <question id="3865" text="Emotions: Comfortable" />
      <question id="4417" text="Lounge: Visited?" />
      <question id="4560" text="Entertnmt: Area?" />
      <question id="4576" text="Club GVC: Member?" />
      <question id="4802" text="PromoPreferred: Large Cash" />
      <question id="4894" text="PromoPreferred: Other" />
      <question id="5255" text="Buffet: Visited?" />
      <question id="5511" text="Casino: Not Played?" />
      <question id="6018" text="Rest: Visited?" />
      <question id="6129" text="Buffet: Comments" />
      <question id="6238" text="Problem: Categorized" />
      <question id="6239" text="Problem: Resolution Comments" />
      <question id="6251" text="Problem: Resolved?" />
      <question id="6253" text="Problem: Comments" />
      <question id="6254" text="Problem: Experience?" />
      <question id="6663" text="Self Parking: Comments" />
      <question id="7598" text="Poker: Played?" />
      <question id="7822" text="Emotions: Entertained low rating explained" />
      <question id="8015" text="Emotions: Important low rating explained" />
      <question id="8033" text="Emotions: Comfortable  low rating explained" />
      <question id="8044" text="Emotions: Respected low rating explained" />
      <question id="8105" text="Loyalty: Not Likely to Return Explained" />
      <question id="8106" text="Loyalty: Not Likely to Recommend Explained" />
      <question id="8225" text="Slots: Played?" />
      <question id="8228" text="PrvLounge: Utilized Host Services?" />
      <question id="8453" text="Poker: Area Cleanliness" />
      <question id="8456" text="PrvLounge: Staff Friendliness" />
      <question id="8457" text="Club GVC: Area Cleanliness" />
      <question id="8479" text="Buffet: Food Presentation" />
      <question id="9008" text="Emotions: Respected" />
      <question id="9068" text="Self Parking: Convenience" />
      <question id="9069" text="Casino: Music Volume" />
      <question id="9389" text="Club GVC: Program Clarity" />
      <question id="9392" text="Tables: Dealer Skill" />
      <question id="9406" text="Slots: Bev Serv Friendliness " />
      <question id="9417" text="Club GVC: Program Usabulity" />
      <question id="9423" text="PrvLounge: Atmosphere" />
      <question id="9431" text="PrvLounge: Visited?" />
      <question id="9466" text="Emotions: Excited" />
      <question id="9975" text="Lounge: Beverage Quality" />
      <question id="10225" text="Tables: Area Cleanliness" />
      <question id="10300" text="Lounge: Area Cleanliness" />
      <question id="10306" text="Buffet: Service Quality" />
      <question id="10309" text="PrvLounge: Service Quality" />
      <question id="10311" text="Rest: FB Quality" />
      <question id="10326" text="PrvLounge: Service Timeliness" />
      <question id="10332" text="PrvLounge: Value" />
      <question id="10334" text="Buffet: FB Quality" />
      <question id="10526" text="Valet: Comments" />
      <question id="11542" text="Tables: Played?" />
      <question id="11552" text="Club GVC: Representatives Knowledge" />
      <question id="13170" text="PromoPreferred: Other Comments" />
      <question id="17928" text="Tables: Limits Variety" />
      <question id="18894" text="Self Parking: Area Cleanliness" />
      <question id="19230" text="Slots: Service Speed" />
      <question id="22326" text="Lounge: Value" />
      <question id="22761" text="Lounge: Service Quality" />
      <question id="23537" text="Buffet: Atmosphere" />
      <question id="23624" text="Cage: Friendliness " />
      <question id="24807" text="Slots: Bev Serv Timeliness" />
      <question id="25063" text="Info: Times Visited per Month?" />
      <question id="25669" text="HL: Dealer Friendliness" />
      <question id="25670" text="HL: Dealer Skill" />
      <question id="25671" text="HL: Bev Serv Friendliness" />
      <question id="25672" text="HL: Bev Serv Timeliness" />
      <question id="25853" text="Poker: FB Quality" />
      <question id="25990" text="Lounge: Atmosphere" />
      <question id="26210" text="Poker: Dealer Friendliness" />
      <question id="26211" text="Poker: Dealer Skill" />
      <question id="26212" text="Poker: Game Variety" />
      <question id="26605" text="PromoPreferred: Table Games" />
      <question id="26623" text="Club GVC: Card Use" />
      <question id="26721" text="PromoPreferred: Slot Tournaments" />
      <question id="27447" text="PrvLounge: Beverage Quality" />
      <question id="27967" text="HL: FB Quality" />
      <question id="27976" text="Tables: FB Quality" />
      <question id="28442" text="Buffet: Variety" />
      <question id="28728" text="Slots: Denominations Variety" />
      <question id="28729" text="Cage: Cashing Availability" />
      <question id="28733" text="Tables: Bev Serv Friendliness" />
      <question id="29272" text="Self Parking: Safety" />
      <question id="29821" text="Buffet: Line Wait-Time" />
      <question id="29823" text="Rest: Variety" />
      <question id="31959" text="Poker: Bev Serv Friendliness" />
      <question id="31960" text="Poker: Bev Serv Timeliness" />
      <question id="32273" text="HL: Played?" />
      <question id="32274" text="HL: Game Variety" />
      <question id="32275" text="HL: Limits Variety" />
      <question id="32276" text="HL: Area Cleanliness" />
      <question id="32277" text="Entertnmt: Area Cleanliness" />
      <question id="32278" text="Entertnmt: Staff Friendliness" />
      <question id="32279" text="Info: How Arrived?" />
      <question id="32280" text="Actions: Name Used" />
      <question id="32281" text="Actions: Quick Service" />
      <question id="32282" text="Actions: Assistance" />
      <question id="32283" text="Actions: Thanked" />
      <question id="32284" text="Actions: Smiled" />
      <question id="32285" text="Info: How Heard About Us?" />
      <question id="32286" text="PromoPreferred: Hot Seat" />
      <question id="32287" text="Info: Primary Reason for Choosing Us? " />
    </questions>
    <participants>
      <participant id="0F8E5F1FEA0E6E63B3F99EBD174A2F95" orgid="128313">
        <attributes>
          <attribute name="Organization"><![CDATA[GVC]]></attribute>
          <importedattribute name="First Name" id="FirstName" datatype="Char"><![CDATA[WILLIAM]]></importedattribute>
          <importedattribute name="Last Name" id="LastName" datatype="Char"><![CDATA[FARRELL]]></importedattribute>
          <importedattribute name="Email Address" id="EmailAddress" datatype="Char"><![CDATA[bfarrell@gonnella.com]]></importedattribute>
          <importedattribute name="Date In" id="ServiceStartDate" datatype="Date">2014-08-17T00:00:00</importedattribute>
          <importedattribute name="Loyalty ID" id="LoyaltyID" datatype="Char"><![CDATA[7120799]]></importedattribute>
          <importedattribute name="Loyalty level" id="LoyaltyLevel" datatype="Char"><![CDATA[3]]></importedattribute>
          <attribute name="Sent Date">2014-08-19T07:44:10.26</attribute>
          <attribute name="Submitted Date">2014-08-19T00:00:00</attribute>
          <attribute name="Survey Score">66.951612903225808</attribute>
        </attributes>
        <responses>
          <response questionid="88" orgid="128313"><![CDATA[Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet. ]]></response>
          <response questionid="108" orgid="128313"><![CDATA[They dont pay out like they once did very tight! ]]></response>
          <response questionid="360" orgid="128313"><![CDATA[Pleasure]]></response>
          <response questionid="750" orgid="128313"><![CDATA[No]]></response>
          <response questionid="906" orgid="128313"><![CDATA[Possibly]]></response>
          <response questionid="907" orgid="128313"><![CDATA[Possibly]]></response>
          <response questionid="1244" orgid="128313"><![CDATA[The comps are low for the amount of money you lose. ]]></response>
          <response questionid="1783" orgid="128313"><![CDATA[Again the payout amounts are lower and less often on the slots. My wife and I have been coming since the boat opened and the attendance is way down. We dont come as often because the pay out is so poor. Smoking laws and Rivers has killed GVC. I dont even smoke but that in it self has people going to Indiana. We also dont like all the kids in the buffet and lobby area this is just wrong on many levels. ]]></response>
          <response questionid="2305" orgid="128313">75</response>
          <response questionid="2326" orgid="128313">100</response>
          <response questionid="2339" orgid="128313">75</response>
          <response questionid="3074" orgid="128313">50</response>
          <response questionid="3265" orgid="128313">50</response>
          <response questionid="3266" orgid="128313">75</response>
          <response questionid="3268" orgid="128313">75</response>
          <response questionid="3271" orgid="128313">100</response>
          <response questionid="3272" orgid="128313">100</response>
          <response questionid="3273" orgid="128313">100</response>
          <response questionid="3274" orgid="128313">25</response>
          <response questionid="3306" orgid="128313">75</response>
          <response questionid="3307" orgid="128313">75</response>
          <response questionid="3308" orgid="128313">50</response>
          <response questionid="3316" orgid="128313">100</response>
          <response questionid="3317" orgid="128313">75</response>
          <response questionid="3319" orgid="128313">25</response>
          <response questionid="3832" orgid="128313">50</response>
          <response questionid="3864" orgid="128313">75</response>
          <response questionid="3865" orgid="128313">100</response>
          <response questionid="4560" orgid="128313"><![CDATA[Yes]]></response>
          <response questionid="4576" orgid="128313"><![CDATA[Yes]]></response>
          <response questionid="4802" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="4894" orgid="128313"><![CDATA[Yes]]></response>
          <response questionid="5511" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="6254" orgid="128313"><![CDATA[No]]></response>
          <response questionid="6663" orgid="128313"><![CDATA[Have never had a problem good lighting.]]></response>
          <response questionid="7598" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="8225" orgid="128313"><![CDATA[Yes]]></response>
          <response questionid="8457" orgid="128313">100</response>
          <response questionid="9008" orgid="128313">50</response>
          <response questionid="9068" orgid="128313">75</response>
          <response questionid="9069" orgid="128313">100</response>
          <response questionid="9389" orgid="128313">75</response>
          <response questionid="9406" orgid="128313">100</response>
          <response questionid="9417" orgid="128313">50</response>
          <response questionid="9466" orgid="128313">50</response>
          <response questionid="11542" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="11552" orgid="128313">100</response>
          <response questionid="13170" orgid="128313"><![CDATA[Just better pay outs very little hand pay outs. ]]></response>
          <response questionid="18894" orgid="128313">50</response>
          <response questionid="19230" orgid="128313">25</response>
          <response questionid="23624" orgid="128313">50</response>
          <response questionid="24807" orgid="128313">100</response>
          <response questionid="25063" orgid="128313"><![CDATA[More than once a month ]]></response>
          <response questionid="26605" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="26623" orgid="128313">100</response>
          <response questionid="26721" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="28728" orgid="128313">75</response>
          <response questionid="28729" orgid="128313"><![CDATA[Does not Apply]]></response>
          <response questionid="29272" orgid="128313">50</response>
          <response questionid="32273" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="32277" orgid="128313">75</response>
          <response questionid="32278" orgid="128313">75</response>
          <response questionid="32279" orgid="128313"><![CDATA[By car, self-parked]]></response>
          <response questionid="32280" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="32281" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="32282" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="32283" orgid="128313"><![CDATA[Yes]]></response>
          <response questionid="32284" orgid="128313"><![CDATA[Yes]]></response>
          <response questionid="32286" orgid="128313"><![CDATA[Not Checked]]></response>
          <response questionid="32287" orgid="128313"><![CDATA[Location]]></response>
          <response questionid="968" orgid="128351"><![CDATA[Better than the buffet. Can take it with you. ]]></response>
          <response questionid="2303" orgid="128351">75</response>
          <response questionid="2304" orgid="128351">50</response>
          <response questionid="3125" orgid="128351">75</response>
          <response questionid="3127" orgid="128351">50</response>
          <response questionid="3290" orgid="128351">75</response>
          <response questionid="3291" orgid="128351">100</response>
          <response questionid="6018" orgid="128351"><![CDATA[Yes]]></response>
          <response questionid="10311" orgid="128351">75</response>
          <response questionid="29823" orgid="128351">75</response>
          <response questionid="4417" orgid="128354"><![CDATA[Not Checked]]></response>
          <response questionid="2292" orgid="128352">50</response>
          <response questionid="3293" orgid="128352">50</response>
          <response questionid="3855" orgid="128352">50</response>
          <response questionid="5255" orgid="128352"><![CDATA[Yes]]></response>
          <response questionid="6129" orgid="128352"><![CDATA[Started out good but has gone down hill! If your a crab leg lover then you have died and gone to heven. What happened to Prime Rib? The stir fry area is never staffed. The beverage stations are just okay and hard to navagate, the plastic glasses are gross! never look clean. Again to many children I dont want to go to a casino and have to listen to a baby cry when I am eating. ]]></response>
          <response questionid="8479" orgid="128352">50</response>
          <response questionid="10306" orgid="128352">50</response>
          <response questionid="10334" orgid="128352">25</response>
          <response questionid="23537" orgid="128352">75</response>
          <response questionid="28442" orgid="128352">25</response>
          <response questionid="29821" orgid="128352">75</response>
          <response questionid="968" orgid="128350"><![CDATA[Last time we went waited over one hour to get our food and walked out! Paid for our beverages.Wont go back.]]></response>
          <response questionid="2303" orgid="128350"><![CDATA[Does not Apply]]></response>
          <response questionid="2304" orgid="128350">75</response>
          <response questionid="3125" orgid="128350">75</response>
          <response questionid="3127" orgid="128350">50</response>
          <response questionid="3290" orgid="128350">1</response>
          <response questionid="3291" orgid="128350">50</response>
          <response questionid="6018" orgid="128350"><![CDATA[Yes]]></response>
          <response questionid="10311" orgid="128350">50</response>
          <response questionid="29823" orgid="128350">75</response>
          <response questionid="6018" orgid="128349"><![CDATA[Not Checked]]></response>
          <response questionid="9431" orgid="128353"><![CDATA[Not Checked]]></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.
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
EXEC sp_xml_removedocument @hDoc

I was hoping to get a list of all the questions and responses but as you can see I am only getting the first one


questionid      orgid      response      attrname      attrId      attrValue
88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       First Name      FirstName      WILLIAM
88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Last Name      LastName      FARRELL
88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Email Address      EmailAddress      bfarrell@gonnella.co
88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Date In      ServiceStartDate      2014-08-17T00:00:00
88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Loyalty ID      LoyaltyID      7120799
88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Loyalty level      LoyaltyLevel      3

Not sure what I am missing
0
Anthony PerkinsCommented:
So what result set are you expecting?
Also this appears to be a duplicate question of this one:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/XML/Q_28503144.html#a40284395
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:
The results I was expecting is the 6 entries I got for question id 88 for all the questions a person answered (not just the first question)

for example  William Farrell, answered 157 questions.  The first one was questioned 88.  The last one had question id
32287.  (question ids are not in order of questions on survey)  All I am getting is the first one (question id 88)   I would like to see entries for all 157

(Note:For the sake of space, I only included results for the first and last question id's)

questionid      orgid      response      attrname      attrId      attrValue
 88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       First Name      FirstName      WILLIAM
 88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Last Name      LastName      FARRELL
 88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Email Address      EmailAddress      bfarrell@gonnella.co
 88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Date In      ServiceStartDate      2014-08-17T00:00:00
 88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Loyalty ID      LoyaltyID      7120799
 88      128313      Get the kids out. Start to loosen up the slots. Have a Prime Rib night in the buffet.       Loyalty level      LoyaltyLevel      3
....
....
 9431      128353   Not Checked      First Name      FirstName      WILLIAM
 9431    128353      Not Checked             Last Name      LastName      FARRELL
9431     128353     Not Checked             Email Address      EmailAddress      bfarrell@gonnella.co
 9431      128353     Not Checked             Date In      ServiceStartDate      2014-08-17T00:00:00
 9431      128353    Not Checked             Loyalty ID      LoyaltyID      7120799
9431      128353    Not Checked            Loyalty level      LoyaltyLevel      3

Hope this makes sense
0
johnnyg123Author Commented:
Thanks for the feed back.  I think I need to head down the xquery path so I will close this and the related one
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/XML/Q_28503144.html#a40284395 

I will then open a question for the xquery

Thanks!
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.