Error Select xml colunn SQL Server

Question

I'm trying to select a column in a table which contains XML using XPath.

This is my query:

SELECT  
    [ServiceRequestHist].id,[ServiceRequestHist].SoapOut,
    CAST(SoapOut AS XML).value(CONVERT(xml, '(/Envelope/Body/InterbankTransferResponse/InterbankTransferResult/AccountFirstHolderName)[1]', 2), 'varchar(100)') AS AccountFirstHolderName,
    CAST(SoapOut AS XML).value(CONVERT(xml,'(/Envelope/Body/InterbankTransferResponse/InterbankTransferResult/AccountNumber)[1]', 2), 'varchar(100)') AS AccountNumber,
    CAST(SoapOut AS XML).value(CONVERT(xml,'(/Envelope/Body/InterbankTransferResponse/InterbankTransferResult/BeneficiaryBank)[1]', 2), 'varchar(100)') AS BeneficiaryBank
FROM 
    [STBMZ_eBankitMonitoring].[dbo].[ServiceRequestHist]

The output is:

Msg 8172, Level 16, State 1, Line 4
The argument 1 of the XML data type method "value" must be a string literal.

Some help?

I would suggest if you are storing xml data that you use the xml datatype. It looks like you are storing this as a varchar? Also, why are you converting your string literal to xml? That is the issue here. Get rid of that convert(xml.... — Sean Lange
– Sean Lange, Commented Apr 27, 2018 at 14:23

Thom A · Accepted Answer · 2018-04-27 14:29:16Z

2

That looks really wrong. You've got a CONVERT in the middle of your value.

This isn't tested, as we have no sample data, however, try:

SELECT  [ServiceRequestHist].id,[ServiceRequestHist].SoapOut,
        CAST(SoapOut AS xml).value('(/Envelope/Body/InterbankTransferResponse/InterbankTransferResult/AccountFirstHolderName/text())[1]', 'varchar(100)') as AccountFirstHolderName,
        CAST(SoapOut AS xml).value('(/Envelope/Body/InterbankTransferResponse/InterbankTransferResult/AccountNumber/text())[1]', 'varchar(100)') as AccountNumber,
        CAST(SoapOut AS xml).value('(/Envelope/Body/InterbankTransferResponse/InterbankTransferResult/BeneficiaryBank/text())[1]','varchar(100)') as BeneficiaryBank
FROM [STBMZ_eBankitMonitoring].[dbo].[ServiceRequestHist];

Firstly, I would suggest changing the datatype of SoapOut to xml in your table; if it's XML store it as xml, not a varchar. I don't actually know if CAST(SoapOut AS XML).value... will work.

Next, also note I used the text() operator, as this will improve performance.

Edit: Again, untested, however, for readability I'd probably do this:

SELECT SRH.id,
       X.SO AS SoapOut,
       SON.ITR.value('/AccountFirstHolderName/text())[1]', 'varchar(100)') as AccountFirstHolderName,
       SON.ITR.value('/AccountNumber/text())[1]', 'varchar(100)') as AccountNumber,
       SON.ITR.value('/BeneficiaryBank/text())[1]','varchar(100)') as BeneficiaryBank
FROM [STBMZ_eBankitMonitoring].[dbo].[ServiceRequestHist] SRH
     CROSS APPLY (VALUES(CONVERT(xml,SRH.SoapOut))) X(SO)
     CROSS APPLY X.SO.nodes('/Envelope/Body/InterbankTransferResponse/InterbankTransferResult/') SON(ITR);

edited Apr 27, 2018 at 14:29

answered Apr 27, 2018 at 14:23

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

4 Comments

Valter Cheque Over a year ago

thanks for answering my question. I tried this before but the output is: "Parsing XML with internal subset DTDs not allowed. Use CONVERT with style option 2 to enable limited internal subset DTD support."

Thom A Over a year ago

In the second example, the style tag would go in the CROSS APPLY, not within the middle of the value operator (which is completely the wrong place).

Valter Cheque Over a year ago

When I executed the query bellow I faced this error: "XQuery [X.SO.nodes()]: Syntax error near '<eof>', expected a step expression.". Solved after removing the last "/" in "X.SO.nodes('/Envelope/Body/InterbankTransferResponse/InterbankTransferResult/')". Now the output message is "XQuery [X.SO.value()]: No more tokens expected at the end of the XQuery expression. Found ')'."

Thom A Over a year ago

@ValterCheque that means you have some malformed xml in your varchar column. Another good reason why you need to store your xml as xml in your database; as you can't stored malformed data. You'll need to find the offending records and fix the xml. You can't CONVERT malformed xml to an xml, so you won't be able to achieve what you're after unless you fix your data.

Collectives™ on Stack Overflow

Error Select xml colunn SQL Server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related