Import xml to sql

Posted on 2014-08-20
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
    LVL 39

    Assisted Solution

    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

    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

    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?
    LVL 75

    Expert Comment

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

    Author Comment

    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[]]></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
    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

    So what result set are you expecting?
    Also this appears to be a duplicate question of this one:

    Author Comment

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

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    In this article I will describe the Backup & Restore 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 get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    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

    18 Experts available now in Live!

    Get 1:1 Help Now