How to return multiple rows from Multiple XML nodes Combine with SQL Tables?

Question

I need to combine XML and data from tables of SQL Server. I am trying to do so and facing following error.

Msg 102, Level 15, State 1, Line 52 Incorrect syntax near 'response'.

XML...

set @sqlxml = N'<response xmlns="http://xyz.in/twa/cmm/decl/v2">
<identification>88762431</identification>
<type>RESPONSE</type>
<submitter><identifier>40134916C</identifier></submitter>
<functionalReference>TSW07389555IM1</functionalReference>
<transactionType>24</transactionType>
<attachDocument>
    <category>AAA</category>
    <mimeCode>application/pdf</mimeCode>
    <URI>16f15574-5d5a-4e83-b9ac-2151f10cf2eb</URI>
    <filename>XYZ_B2021_199.pdf</filename>
</attachDocument>
<attachDocument>
    <category>AAB</category>
    <mimeCode>text/plain</mimeCode>
    <URI>1511b476-a2be-4ae5-a54c-0a5dc14759b2</URI>
    <filename>XYZ_B2021_199_xml.txt</filename>
</attachDocument>
<additionalInformationICN><text>Please refer to attached XYZ for Directions</text></additionalInformationICN>
<issueDate>20210331113355</issueDate>
<overallDeclaration>
    <identification>88762431</identification>
    <functionalReference>TSW07389555IM1</functionalReference>
    <submitter>
        <identifier>40134916C</identifier>
    </submitter>
    <responsibleGovernmentAgency>XYZ</responsibleGovernmentAgency>
</overallDeclaration>
<status>
    <agency>XYZ</agency>
    <effectiveDate>20210331113355</effectiveDate>
    <name>B04</name>
    <releaseDate>20210331113355</releaseDate>
</status>
</response>'

SQL Script...

;WITH XMLNAMESPACES ('http://xyz.in/twa/cmm/decl/v2' AS ns)
SELECT distinct lv.[VERSION] LocationVersion,
lv.CHANGE_REASON LocationVersionChangeReason,
lv.CREATED_TIMESTAMP LocationVersionCreatedTimestamp,
lrd.CATEGORY,
lrd.MIME_TYPE as mimecode,
coalesce(lrd.[OBJECT_ID], lrd.FILENET_ID) as URI,
lrd.DOCUMENT_NAME as [FileName],
cast(lrd.SEQUENCE as bigint) sequence
response.value('(/response/status/agency/text())[1]','varchar(100)') as ResponseAgency,
response.value('(/response/issueDate/text())[1]','varchar(50)') as ResponseIssueDateTime,
response.value('(/response/additionalInformationICN/text/text())[1]','varchar(1000)') as ResponseClearanceInstructions
FROM DB.Location l
INNER JOIN DB.Location_RESPONSE lr ON l.Location_ENTITY_KEY = lr.Location_ENTITY_KEY
INNER JOIN DB.Location_RESPONSE_DOCUMENT lrd ON lr.Location_RESPONSE_KEY = lrd.Location_RESPONSE_KEY
LEFT OUTER JOIN DB.Location_VERSION lv on lr.Location_VERSION_KEY = lv.Location_VERSION_KEY
INNER JOIN DB.Location_RESPONSE lr2 on l.Location_ENTITY_KEY = lr2.Location_ENTITY_KEY
CROSS APPLY @sqlxml.nodes('/response') AS xmltable(response)
WHERE l.Llocation_ENTITY_KEY = 123456789

Sql script is working fine return data from database without including XML as below...

;WITH XMLNAMESPACES ('http://xyz.in/twa/cmm/decl/v2' AS ns)
SELECT distinct lv.[VERSION] LocationVersion,
lv.CHANGE_REASON LocationVersionChangeReason,
lv.CREATED_TIMESTAMP LocationVersionCreatedTimestamp,
lrd.Category,
lrd.MIME_TYPE as Mimecode,
coalesce(lrd.[OBJECT_ID], lrd.FILENET_ID) as URI,
lrd.DOCUMENT_NAME as [FileName],
cast(lrd.SEQUENCE as bigint) [Sequence]
--response.value('(/response/status/agency/text())[1]','varchar(100)') as ResponseAgency,
--response.value('(/response/issueDate/text())[1]','varchar(50)') as ResponseIssueDateTime,
--response.value('(/response/additionalInformationICN/text/text())[1]','varchar(1000)') as ResponseClearanceInstructions
FROM DB.Location l
INNER JOIN DB.Location_RESPONSE lr ON l.Location_ENTITY_KEY = lr.Location_ENTITY_KEY
INNER JOIN DB.Location_RESPONSE_DOCUMENT lrd ON lr.Location_RESPONSE_KEY = lrd.Location_RESPONSE_KEY
LEFT OUTER JOIN DB.Location_VERSION lv on lr.Location_VERSION_KEY = lv.Location_VERSION_KEY
INNER JOIN DB.Location_RESPONSE lr2 on l.Location_ENTITY_KEY = lr2.Location_ENTITY_KEY
--CROSS APPLY @sqlxml.nodes('/response') AS xmltable(response)
WHERE l.Llocation_ENTITY_KEY = 123456789

Result...

Can someone help pls?

Thanks in Advance

You forgot your comma at the end of the line cast(lrd.SEQUENCE as bigint) sequence — Bee_Riii
– Bee_Riii, Commented May 6, 2021 at 7:59
As Larnu is often fond of saying... ; is a statement terminator, not a statement beginninator. It's bad form to have ;WITH anything. — AlwaysLearning
– AlwaysLearning, Commented May 6, 2021 at 10:12
Try using WITH XMLNAMESPACES (default 'http://xyz.in/twa/cmm/decl/v2') instead. — AlwaysLearning
– AlwaysLearning, Commented May 6, 2021 at 10:13
Also try removing /response/ from your response.value() XPaths ... @sqlxml.nodes() has already selected the /response element so /response/... is redundant. — AlwaysLearning
– AlwaysLearning, Commented May 6, 2021 at 10:14
Unclear why you have .nodes there at all, there is only one <response> node — Charlieface
– Charlieface, Commented May 6, 2021 at 10:46

Yitzhak Khabinsky · Accepted Answer · 2021-05-06 15:06:42Z

2

What you are doing with XML is called shredding. It converts XML data type into a rectangular/relational format. After that you need to join it with the rest of data in some other DB tables.

The easiest way to achieve it is via CTE. And you need to join data/columns from the resultset rs.* with the rest of the DB tables.

Along the following:

;WITH XMLNAMESPACES (DEFAULT 'http://xyz.in/twa/cmm/decl/v2'), rs AS
(
    SELECT c.value('(category/text())[1]','VARCHAR(50)') as Category,
        c.value('(mimeCode/text())[1]','VARCHAR(50)') as MimeCode,
        c.value('(URI/text())[1]','UNIQUEIDENTIFIER') as URI,
        c.value('(filename/text())[1]','VARCHAR(50)') as [FileName],
        c.value('(/response/status/agency/text())[1]','VARCHAR(100)') as ResponseAgency,
        c.value('(/response/issueDate/text())[1]','VARCHAR(100)') as ResponseIssueDateTime,
        c.value('(/response/additionalInformationICN/text/text())[1]','VARCHAR(100)') as ResponseClearanceInstructions
   FROM @sqlxml.nodes('/response/attachDocument') AS t(c)
)
SELECT * 
FROM rs INNER JOIN ...;

edited May 6, 2021 at 15:06

answered May 6, 2021 at 14:39

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

SQLHoncho Over a year ago

Thanks Yitzhak.. are you able to refer any good article... would be helpful (or an example will help with thought around it).

Yitzhak Khabinsky Over a year ago

@SQLHoncho, please connect with me on LinkedIn. We will have a chat on the subject.

SQLHoncho Over a year ago

Done and with you :)

Collectives™ on Stack Overflow

How to return multiple rows from Multiple XML nodes Combine with SQL Tables?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related