Import xml to sql

Posted on 2014-08-20
Medium Priority
Last Modified: 2014-08-26
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

      @XMLData NTEXT,
      @myId SMALLINT

-- Retrieve XML data

DECLARE @Comments NVARCHAR(2048)


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

      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

-- 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

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
Question by:johnnyg123
  • 5
  • 2
LVL 40

Assisted Solution

lcohan earned 1000 total points
ID: 40273838
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:


Author Comment

ID: 40274098
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

<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"/>

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">
</attribute><importedattribute name="First Name" id="FirstName" datatype="Char">
</importedattribute><importedattribute name="Last Name" id="LastName" datatype="Char">
</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">
</importedattribute><importedattribute name="Loyalty level" id="LoyaltyLevel" datatype="Char">
</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">
</response><response orgid="128313" questionid="750">
</response><response orgid="128313" questionid="906">
<![CDATA[Very likely]]>
</response><response orgid="128313" questionid="907">
<![CDATA[Very likely]]>

Author Comment

ID: 40274254
I tried this to get list of questions but get no data



EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML

SELECT QuestionID, Text
FROM OPENXML(@hDoc, 'dataextract/dataextract/questions')
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"?>



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




<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"/>



<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?"/>




what am I doing wrong?
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 40280754
That Xml is not well-formed.  It does not have a top level element and dataextract does not have a closing tag.

Author Comment

ID: 40284380
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>
      <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" />
      <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? " />
      <participant id="0F8E5F1FEA0E6E63B3F99EBD174A2F95" orgid="128313">
          <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>
          <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>
 --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
LVL 75

Accepted Solution

Anthony Perkins earned 1000 total points
ID: 40284538
So what result set are you expecting?
Also this appears to be a duplicate question of this one:

Author Comment

ID: 40285930
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

Author Closing Comment

ID: 40286703
Thanks for the feed back.  I think I need to head down the xquery path so I will close this and the related one

I will then open a question for the xquery


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

627 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