TSQL Read XMl File

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

*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" />
*/SubmissionResults>

Please post xml using the three code ticks

`

Hi Sorry , can you explain three code ticks ?

3 of this in front of xml ==> ` <== and three in back

without ticks

Tove

Jani

Reminder

Don't forget me this weekend!

with ticks

<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
<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" />
 </SubmissionResults>
1 Like

so now from this xml in a sql table column what do you want to extract again, original post looks messy

use sqlteam
go

if OBJECT_ID('tempdb..#goldfinger') is not null
	drop table #goldfinger

create table #goldfinger(deathstar xml)

insert into #goldfinger
select '<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" />
 </SubmissionResults>'

 select * from #goldfinger


Hi Yosiasz,

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)

but I cannot get any result from s.c etc.

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.

I'm trying to get

15864428077959YRfW_g89	complete	live	008447	Thu Apr 09 15:33:33 BST 2020	2020-04-14	NULL	NULL	NULL

but cannot get the UserNumber or name

usernumber should be 123123
name should be MY NAME

but all I am getting back is NULL values

those values are under SubmissionResults. you do not have such values under SubmittingServiceUser

here is one way using openxml

DECLARE @XMLText XML = (select deathstar from #peter)

--OPENXML
DECLARE @hdoc int
    
EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLText
SELECT *
FROM OPENXML (@hdoc, '/SubmissionResults' , 1)
WITH(
    status VARCHAR(100),
	submissionIdentifier  VARCHAR(100),
	submissionType VARCHAR(100),
	submissionSerialNumber VARCHAR(100),
	submissionDateAndTime VARCHAR(100),
	submissionEarliestDate datetime,
	userNumber varchar(100) './SubmittingServiceUser/@userNumber',
	name varchar(100) './SubmittingServiceUser/@name',
	contactIdentifier VARCHAR(100) './SubmittingContact/@contactIdentifier',
	fullName VARCHAR(100) './SubmittingContact/@fullName',
	contactIdentifier_sc VARCHAR(100) './SigningContact/@contactIdentifier',
	fullName_sc VARCHAR(100) './SigningContact/@fullName'	
    )
    
EXEC sp_xml_removedocument @hdoc
1 Like

Another way

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 
1 Like