Previously I thought I had this issue cracked. Trying to read an xml file stored in a database for use in a query. The XML file is generated by a third party and imported as is. I have tried using XML nodes to read this but have to use /* as root. This is fine for the root elements but I cannot get any other data
I need to get
SubmissionResults
status="complete"
submissionIdentifier="15864428077959YRfW_g89"
submissionType="live"
submissionSerialNumber="008447"
submissionDateAndTime="Thu Apr 09 15:33:33 BST 2020"
submissionEarliestDate="2020-04-14"
SubmittingServiceUser
userNumber="123123"
name="MY NAME"
SubmittingContact
contactIdentifier="Bond007"
fullName="James Bond"
SigningContact
contactIdentifier="Bat01"
fullName="Batman"
< replaced by * to post
The xml code is already stored in a sql table, I'm trying to read it out using ;
DECLARE @XMLText XML = (SELECT CAST([new_submissionresult] as XML) FROM [xxx_MSCRM].[dbo].[new_xx] WHERE CAST(new_bacssubmissionid AS VARCHAR(100)) = @mosaicreportid)
SELECT a.c.value('@submissionIdentifier[1]', 'VARCHAR(100)') AS 'submissionIdentifier',
a.c.value('@status[1]', 'VARCHAR(10)') AS 'status',
a.c.value('@submissionType[1]', 'VARCHAR(10)') AS 'submissionType',
a.c.value('@submissionSerialNumber[1]', 'VARCHAR(10)') AS 'submissionSerialNumber',
a.c.value('@submissionDateAndTime[1]', 'VARCHAR(100)') AS 'submissionDateAndTime',
a.c.value('@submissionEarliestDate[1]', 'VARCHAR(10)') AS 'submissionEarliestDate',
a.c.value('@userNumber[1]', 'VARCHAR(10)') AS 'submissionEarliestDate',
s.c.value('@userNumber[1]', 'VARCHAR(10)') AS 'userNumber',
s.c.value('@name[1]', 'VARCHAR(100)') AS 'name'
FROM (SELECT @XMLText AS 'Xmlreport') d
CROSS APPLY Xmlreport.nodes('/*') AS a(c)
OUTER APPLY a.c.nodes('/SubmittingServiceUser') s(c)
you have it stored in your sql table but we do not have access to you sql server. So we have to create a sample table that emulates your environment to give you a more accurate answer. Sample data is a must in a lot of cases.
So you have still not answered the question. What is the final result you want to see? You posted your SQL code not the result you wanted to see.
DECLARE @XMLText XML = (select deathstar from #peter)
SELECT Xmlreport.value('(/SubmissionResults/@submissionIdentifier)[1]', 'VARCHAR(100)') AS 'submissionIdentifier',
Xmlreport.value('(/SubmissionResults/@status)[1]', 'VARCHAR(10)') AS 'status',
Xmlreport.value('(/SubmissionResults/@submissionType)[1]', 'VARCHAR(10)') AS 'submissionType',
Xmlreport.value('(/SubmissionResults/@submissionSerialNumber)[1]', 'VARCHAR(10)') AS 'submissionSerialNumber',
Xmlreport.value('(/SubmissionResults/@submissionDateAndTime)[1]', 'VARCHAR(100)') AS 'submissionDateAndTime',
Xmlreport.value('(/SubmissionResults/@submissionEarliestDate)[1]', 'VARCHAR(10)') AS 'submissionEarliestDate',
Xmlreport.value('(/SubmissionResults/@userNumber)[1]', 'VARCHAR(10)') AS 'submissionEarliestDate',
Xmlreport.value('(/SubmissionResults/SubmittingServiceUser/@userNumber)[1]', 'VARCHAR(10)') AS 'userNumber',
Xmlreport.value('(/SubmissionResults/SubmittingServiceUser/@name)[1]', 'VARCHAR(100)') AS 'name',
Xmlreport.value('(/SubmissionResults/SubmittingContact/@contactIdentifier)[1]', 'varchar(100)') as 'contactIdentifier'
from (SELECT @XMLText AS 'Xmlreport') d