CODEWITHSUNDEEP
xmlt-sqlxsd
JBlack7708
JBlack7708

Reputation: 3

T-SQL returning null when reading XML File

I am pretty new to working with XML. I need to import the following XML, and everything I have tried has returned null values.

XML:

<instance xmlns="http://www.devicemagic.com/xforms/e3a08280-797d-0132-1d7a-22000b3609ea" xmlns:dm="http://mobileforms.devicemagic.com/xforms" xmlns:dm_device="http://mobileforms.devicemagic.com/xforms/custom_device_attributes" submittingDevice="Android_355794055830490" submissionIdentifier="528f6bf6-1f5e-42aa-bef0-b4c339df755f" writeTime="2015-07-21T08:56:53-0600" formVersion="1.05" dm:submitting_user="Jeremy" dm:submitting_device="Android_355794055830490" dm:submit_time="2015-07-21T14:56:54Z" dm:form="Daily Timesheet &amp; Job Details" dm:form_id="4506512" dm:form_version="1.05" dm:submission_id="7517417">
  <inputs>
    <Work_Date>2015-07-21</Work_Date>
    <Job_Details>
      <Stratty_Tech>John Doe</Stratty_Tech>
      <Job_Number>2222</Job_Number>
      <Type_Of_Work>Course</Type_Of_Work>
      <Directions_To_Location>go here and there</Directions_To_Location>
      <Stand_By_Hours>0</Stand_By_Hours>
      <Set_up_Hours>8</Set_up_Hours>
      <Travel_Hours>0</Travel_Hours>
      <Kilometers>0</Kilometers>
      <Unit_No>122</Unit_No>
      <Include_Personal_Truck>false</Include_Personal_Truck>
      <Jobs_Performed>Labour</Jobs_Performed>
      <Garbage_Removal>false</Garbage_Removal>
    </Job_Details>
    <Include_Shop_TIme_>false</Include_Shop_TIme_>
    <Total_Stand_By_Hours>0</Total_Stand_By_Hours>
    <Total_Set_up_Hours>8</Total_Set_up_Hours>
    <Total_Travel_Hours>0</Total_Travel_Hours>
    <Total_Personal_Truck>0</Total_Personal_Truck>
    <Total_Field_Hours>8</Total_Field_Hours>
    <Total_Hours>8</Total_Hours>
  </inputs>
</instance>

I have also tried using SSMS to create xsd files, and its giving me errors about not declaring matching namespaces. Ideally I would like to automate this import using T-SQL. Any help would be greatly appreciated.

Edit.
I've tried this after being able to import it into XML_Import table. 

DECLARE @MyXML XML

SET @MyXML = (SELECT CAST(XMLCOL As XML)  FROM XML_Import)


SELECT xmlData.Col.value('Work_Date[1]', 'varchar(max)') As Work_Date
FROM @MyXML.nodes('//instance/inputs[1]') xmlData(Col);

Upvotes: 0

Views: 56

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5694

The problem is caused by the namespace. You can use one of these ways to specify the default namespace:

SELECT xmlData.Col.value('declare default element namespace "http://www.devicemagic.com/xforms/e3a08280-797d-0132-1d7a-22000b3609ea";Work_Date[1]', 'varchar(max)') As Work_Date
FROM @MyXML.nodes('declare default element namespace "http://www.devicemagic.com/xforms/e3a08280-797d-0132-1d7a-22000b3609ea";//instance/inputs[1]') xmlData(Col);

;WITH XMLNAMESPACES (DEFAULT 'http://www.devicemagic.com/xforms/e3a08280-797d-0132-1d7a-22000b3609ea')
SELECT xmlData.Col.value('Work_Date[1]', 'varchar(max)') As Work_Date
FROM @MyXML.nodes('//instance/inputs[1]') xmlData(Col);

See https://msdn.microsoft.com/en-us/library/ms187013.aspx and https://msdn.microsoft.com/en-us/library/ms177400.aspx

Upvotes: 1

Related Questions