SQL XML load with OpenRow

Question

I am trying to load a XML document (below is an example of how I receive the files. I can read the xml, but I am unable to insert the the data into tables. Any help would be appreciative.

<xml xmlns:dt="urn:dt" xmlns:msxsl="urn:schemas-microsoft-com:xslt" dateofservice="1/1/2016 10:00" mmsid="201599999999" userid="dxxxxx9-xxx0-xxdb-xxx0-e8xxxxxxbcd" npid="dfxxxxx9-6xx0-xxxx-bxx0-exxxc1xxxxxd" surveyid="xxxxxxx-xxxa-exxx-8xxx-xxxx56xxxxefb" memberid="sqlsrfr">
  <response qid="801" debug="7" value="H" element="select" />
  <response qid="150" debug="8" value="Surfer" element="input" mapfield="lastname" />
  <response qid="109" debug="9" value="Sequel" element="input" mapfield="firstname" />
  <response qid="57" debug="11" value="01/01/1901" element="input" mapfield="dob" />
  <response qid="56" debug="12" value="M" element="input" type="radio" aid="85" />
  <response qid="78" debug="13" value="123 Sequel Lane" element="textarea" mapfield="addr1" />
  <response qid="126" debug="39" value="Stuff" element="input" row="9" placeholder="Placeholder Desc" customtype="disabled" />
  <response qid="128" debug="40" value="Stuff" element="input" row="9" placeholder="Placeholder Desc" customtype="disabled" />
  <response qid="305" debug="41" value="More words" element="input" row="9" placeholder="Placeholder Desc" customtype="normal" />
  <response qid="579" debug="330" value="1" element="input" type="radio" /> 
  <response qid="580" debug="331" value="1" element="input" type="radio" /> 
  <response qid="716" value="Words for value" calc="1" screening="1" /> 
  <response qid="779" value="More words for value" calc="1" highriskdrug="1" /> 
- <surveyevents>
  <event name="Event Name 2.0.3.7" time="1451495565657" count="1" /> 
  <event name="s2" time="1451495568305" count="2" last="1451495728416" /> 
  <event name="s3" time="1451495577298" count="1" /> 
  <event name="s18" time="1451495601418" count="1" /> 
  <event name="Event Name 2.0.3.7" time="1451495725279" count="1" /> 
  <event name="Event Name 2.0.4.1" time="1453394485181" count="1" /> 
  </surveyevents>
  <recapturedata /> 
  </xml>

Please do always state your full RDBMS (vendor and version), show what you've tried already ("... can read the xml...") and state the expected output (in this case your target table(s). — Gottfried Lesigang
– Gottfried Lesigang, Commented Jan 26, 2016 at 8:58

Gottfried Lesigang · Accepted Answer · 2016-01-26 09:20:25Z

Given, that you manage to read the XML into a variable, the full (de-normalized) query is this:

You will have to design three tables for MetaData, ResponseData and EventData and insert your data there.

If you need to create kind of IDs have a look on ROW_NUMBER() OVER()

btw: The declared namespaces are not used... and define the fitting datatypes yourself (I took only varchar(max) or int).

WITH XMLNAMESPACES('urn:dt' AS dt
                  ,'urn:schemas-microsoft-com:xslt' AS msxsl)
,MetaData AS
(
    SELECT @xml.value('/xml[1]/@dateofservice','varchar(max)') AS md_DateOfService
          ,@xml.value('/xml[1]/@mmsid','varchar(max)') AS md_MmsID
          ,@xml.value('/xml[1]/@userid','varchar(max)') AS md_UserID
          ,@xml.value('/xml[1]/@npid','varchar(max)') AS md_NpID
          ,@xml.value('/xml[1]/@surveyid','varchar(max)') AS md_SurveyID
          ,@xml.value('/xml[1]/@memberid','varchar(max)') AS md_MemberID
          ,@xml.query('.') AS XMLNode
)
SELECT md.md_DateOfService
      ,md.md_MmsID
      ,md.md_UserID
      ,md.md_NpID
      ,md.md_SurveyID
      ,md.md_MemberID
      ,response.value('@qid','int') AS resp_qID
      ,response.value('@debug','int') AS resp_Debug
      ,response.value('@value','varchar(max)') AS resp_Value
      ,response.value('@element','varchar(max)') AS resp_Element
      ,response.value('@row','int') AS resp_Row
      ,response.value('@mapfield','varchar(max)') AS resp_MapField
      ,response.value('@type','varchar(max)') AS resp_Type
      ,response.value('@aid','int') AS resp_aID
      ,response.value('@placeholder','varchar(max)') AS resp_Placeholder
      ,response.value('@customtype','varchar(max)') AS resp_CustomType
      ,EventRow.value('@name','varchar(max)') AS evnt_Name
      ,EventRow.value('@time','varchar(max)') AS evnt_Time
      ,EventRow.value('@count','int') AS evnt_Count
      ,EventRow.value('@last','varchar(max)') AS evnt_Last
FROM MetaData AS md
CROSS APPLY md.XMLNode.nodes('/xml/response') AS One(response) 
CROSS APPLY md.XMLNode.nodes('/xml/surveyevents/event') AS The(EventRow)

Thanks Shnugo, I was very close and the eventrow along with xmlnode was what I was having touble with.

Collectives™ on Stack Overflow

SQL XML load with OpenRow

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related