2

I have two separate data input tables from different sources (one direct insert, one via API). I´m trying to fetch a unified list of tuples out of these two tables using the UNION command. Unfortunately the information of the field SourceEventID has different source types:

In table1, the information for SourceEventID is stored inside an xml in the field IncidentRequest_XML (type=xml).
In table2 the data for this field is stored as a string (type = nvarchar).

My questions are:

Is it possible to get the result by using the union command in a different way and if not, how is it possible to get the unified list out of the two tables with the told circumstances?

Thanks a lot!

I tried to get the result using the UNION command but it told me, the following:

The data type xml cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable. 

SELECT
    row_number() OVER (PARTITION BY A.SourceSystem ORDER BY Date) as Row,
    IncidentRequest_XML.query('INCIDENT_REQUEST/SourceEventID') as SourceEventID,
    'FOO' as Type
FROM dbo.Source_A 
INNER JOIN dbo.ConfigTable C ON A.SourceSystem = C.SourceSystem
WHERE C.ReturnStatus = 1
UNION
SELECT
    row_number() OVER (PARTITION BY B.SourceSystem ORDER BY Date) as Row,
    SourceEventID,
    'BAR' as Type
FROM dbo.Source_B
INNER JOIN dbo.ConfigTable C ON B.SourceSystem = C.SourceSystem
WHERE C.ReturnStatus = 1

What I expect is the following result:

ROW     SourceEventID                              Type
1       <SourceEventID>abc12345</SourceEventID>    FOO
2       testcall123                                BAR
Meraj al Maksud
  • 1,528
  • 2
  • 22
  • 36
Foster80
  • 23
  • 3

1 Answers1

0

If you look at your expected result you see, that XML and string would be mixed in the same column. This is not possible...

What you can do, is either use .value() instead of .query() to read the SourceEventId as string:

IncidentRequest_XML.value('(INCIDENT_REQUEST/SourceEventID/text())[1]','nvarchar(100)')  as SourceEventID

Or you can use a cast, to transform your result of .query() to a string:

CAST(IncidentRequest_XML.query('INCIDENT_REQUEST/SourceEventID') AS NVARCHAR(100)) as SourceEventID

Good to know: .query() returns a XML-typed result, which is the result of a XQuery-expression. On the other hand .value() can deal with a singleton value only (therefore we need the (blah)[1] and the specific type.

Shnugo
  • 66,100
  • 9
  • 53
  • 114