No Records Returned When Parsing XML

Transact-SQL
1

Hello. I'm new to parsing XML in T-SQL.

We are storing XML in a field in our Azure SQL Server database. Here's an example of the XML:

<ns3:FormBCTransmitterSubmissionDtl xmlns="urn:us:gov:treasury:irs:ext:aca:air:ty23" xmlns:ns2="urn:us:gov:treasury:irs:common" xmlns:ns3="urn:us:gov:treasury:irs:msg:form1094-1095BCtransmittermessage">
	<ACATransmitterSubmissionDetail>
		<TransmitterErrorDetailGrp>
			<SubmissionLevelStatusCd>Rejected</SubmissionLevelStatusCd>
			<UniqueSubmissionId>AATS1094C-24-00018777|1</UniqueSubmissionId>
		</TransmitterErrorDetailGrp>
		<TransmitterErrorDetailGrp>
			<UniqueSubmissionId>AATS1094C-24-00018777|1</UniqueSubmissionId>
			<ns2:ErrorMessageDetail>
				<ns2:ErrorMessageCd>1094C-098</ns2:ErrorMessageCd>
				<ns2:ErrorMessageTxt>If 'TestFileCd' has a value of "T", and the 1094-C 'EmployerEIN' in 'EmployerInformationGrp' is present, then it must match the reference data set.</ns2:ErrorMessageTxt>
				<ns2:XpathContent>/Form109495CTransmittalUpstream/Form1094CUpstreamDetail/EmployerInformationGrp/EmployerEIN</ns2:XpathContent>
			</ns2:ErrorMessageDetail>
		</TransmitterErrorDetailGrp>
		<TransmitterErrorDetailGrp>
			<UniqueRecordId>AATS1094C-24-00018777|1|90</UniqueRecordId>
			<ns2:ErrorMessageDetail>
				<ns2:ErrorMessageCd>1095C-071</ns2:ErrorMessageCd>
				<ns2:ErrorMessageTxt>If 'TestFileCd' has a value of "T" and the 1095C has an 'SSN' present within 'EmployeeInfoGrp', then it must match the reference data set.</ns2:ErrorMessageTxt>
				<ns2:XpathContent>/Form109495CTransmittalUpstream/Form1094CUpstreamDetail/Form1095CUpstreamDetail/EmployeeInfoGrp/SSN</ns2:XpathContent>
			</ns2:ErrorMessageDetail>
		</TransmitterErrorDetailGrp>
		<TransmitterErrorDetailGrp>
			<UniqueRecordId>AATS1094C-24-00018777|1|90</UniqueRecordId>
			<ns2:ErrorMessageDetail>
				<ns2:ErrorMessageCd>1095C-012-01</ns2:ErrorMessageCd>
				<ns2:ErrorMessageTxt>If Form 1095C 'AnnualOfferOfCoverageCd' does not have a value, then all occurrences of 'MonthlyOfferCoverageGrp' (JanOfferCd through DecOfferCd) must have a value.</ns2:ErrorMessageTxt>
				<ns2:XpathContent>/Form109495CTransmittalUpstream/Form1094CUpstreamDetail/Form1095CUpstreamDetail/EmployeeOfferAndCoverageGrp/AnnualOfferOfCoverageCd</ns2:XpathContent>
			</ns2:ErrorMessageDetail>
		</TransmitterErrorDetailGrp>
	</ACATransmitterSubmissionDetail>
</ns3:FormBCTransmitterSubmissionDtl>

What I want to do is parse the above XML so I get these records like this:

sqlteam topic xml
sqlteam topic xml1506×78 7.18 KB

Here's what I have so far:

declare @xml as xml

select @xml=errorxml from ACATransmissions where id=175

;with xmlnamespaces ('urn:us:gov:treasury:irs:ext:aca:air:ty23' as ns3, 'urn:us:gov:treasury:irs:common' as ns2)

select xt.child.value('@ns2:ErrorMessageTxt', 'VARCHAR(500)') AS ErrorMessage,
xt.child.value('@ns2:XpathContent', 'varchar(500)') AS XPath
from @xml.nodes('/ns3:FormBCTransmitterSubmissionDtl/ACATransmitterSubmissionDetail/TransmitterErrorDetailGrp/ns2:ErrorMessageDetail') as xt(child)

However, this query isn't returning any records. I'm not getting any errors - just no records. Does anyone have any idea what I'm doing wrong? Thanks.

2

A few things:

  1. Looks like you have your namespaces mixed up.
  2. You need to specify the default namespace, or alias it and use the alias throughout.
  3. You use the @ symbol for attributes, not for nodes.

Here is my revised code:

;WITH XMLNAMESPACES
     (
         'urn:us:gov:treasury:irs:msg:form1094-1095BCtransmittermessage' AS ns3,
         'urn:us:gov:treasury:irs:common' AS ns2,
         DEFAULT 'urn:us:gov:treasury:irs:ext:aca:air:ty23' 
     )
SELECT
    xt.child.value ('ns2:ErrorMessageTxt[1]', 'VARCHAR(500)') AS ErrorMessage,
    xt.child.value ('ns2:XpathContent[1]', 'varchar(500)')    AS XPath
FROM
    @xml.nodes('/ns3:FormBCTransmitterSubmissionDtl/ACATransmitterSubmissionDetail/TransmitterErrorDetailGrp/ns2:ErrorMessageDetail') AS xt(child);
1 Like
3

Thank you James!