Bulk Insert XML...help

• rodto3rd

  SSC Enthusiast

  Points: 197

  More actions

  January 25, 2016 at 11:34 am

  #304058

  I am trying to load a XML document (below is an example of how I receive the files). I believe it is the namespaces messing me up, but cannot pull out the relevant data. 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>

• drew.allen

  SSC Guru

  Points: 76776

  More actions

  January 25, 2016 at 12:26 pm

  #1854272

  There are two basic approaches you can use:

  1) Use XMLNAMESPACES

  2) Use a wild card for the namespace.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  January 25, 2016 at 2:29 pm

  #1854300

  No Namespace issues as far as I can tell. Here's a couple examples of how to parse your XML data:

  DECLARE @xml XML =

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

  SELECT

  qid = x.value('(@qid)[1]','int'),

  element = x.value('(@element)[1]', 'varchar(100)'),

  value = x.value('(@value)[1]','varchar(100)')

  FROM (VALUES (@xml)) X(v)

  CROSS APPLY v.nodes('/xml/response') xx(x);

  SELECT

  name = x.value('(@name)[1]','varchar(100)'),

  timeTxt = x.value('(@time)[1]','bigint'),

  TotalCount = x.value('(@count)[1]','bigint')

  FROM (VALUES (@xml)) X(v)

  CROSS APPLY v.nodes('/xml/surveyevents/event') xx(x);

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001