Reputation: 3
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 & 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
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