February 20, 2020 at 1:20 am
Scenario 1 works just fine
DECLARE @XMLT XML = '<Header>
<ReportDate>10032017</ReportDate>
<ReportTime>140607</ReportTime>
<Preamble>TWA1</Preamble>
<ARFVersion>07</ARFVersion>
</Header>'
SELECT @XMLT AS TheXML;
select
Header.DT.value('ReportDate[1]' ,'VARCHAR(10)') AS ReportDate
,Header.DT.value('ReportTime[1]' ,'VARCHAR(10)') AS ReportTime
,Header.DT.value('Preamble[1]' ,'VARCHAR(10)') AS Preamble
,Header.DT.value('ARFVersion[1]' ,'VARCHAR(10)') AS ARFVersion
FROM @XMLT.nodes('Header') AS Header(DT);
Scenario 2
but i have xml that has data like (highlighted in bold in the xml) and i get xml parsing error
(XML parsing: line 6, character 21, A string literal was expected)
DECLARE @XMLT XML = '<Header>
<ReportDate>10032017</ReportDate>
<ReportTime>140607</ReportTime>
<Preamble>TWA1</Preamble>
<ARFVersion>07</ARFVersion>
<Evaluation code=\"P\"/>
</Header>'
February 20, 2020 at 9:57 pm
I believe your issue here is that code is not being provided a string literal which is required by the XML 1.0 standards.
Similar issue to yours, but slightly different:
His issue was that his value was 99 without quotes. Putting quotes in should fix it. In your example, you have a character prior to the quote (\) so it is not a string literal. you will need to remove the leading \ and it'll be fixed.
Also check out:
http://www.featureblend.com/xml-attribute-quoting.html
for XML attribute quoting. In your code, "code" is an attribute of Evaluation. If the attribute MUST be \"P\", you will need to quote that with ' such as:
DECLARE @XMLT XML = '<Header>
<ReportDate>10032017</ReportDate>
<ReportTime>140607</ReportTime>
<Preamble>TWA1</Preamble>
<ARFVersion>07</ARFVersion>
<Evaluation code=''\"P\"''/>
</Header>'
SELECT @XMLT
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 21, 2020 at 3:47 pm
Sometimes a backslash is used to insert a literal character. It looks like whatever produced this XML hypercorrected and used \" to insert a literal double quote (") when it wasn't necessary, so it is being displayed in the final output rather than being interpreted as an escape character. The backslash is what is causing your error.
XML uses double quotes (") not two single quotes ('') to delimit attribute values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 21, 2020 at 4:15 pm
Drew, I think that both single and double quotes are valid according to the XML standard (or at least my interpretation of it).
Microsoft's interpretation in the .NET framework allows for single or double quotes as well:
https://docs.microsoft.com/en-us/previous-versions/dotnet/netframework-4.0/ms256152(v=vs.100)?redirectedfrom=MSDN
The official XML standard is a bit lengthy and difficult to understand but is available here:
https://www.w3.org/TR/REC-xml/
Microsoft has examples where they use single quote and double quote along with how to show a single or double quote inside the XML when it starts with a single or double quote. The XML standard, I interpreted this from section 2.4 which ends with:
To allow attribute values to contain both single and double quotes, the apostrophe or single-quote character (') may be represented as " ' ", and the double-quote character (") as " " ".
I am not sure why you'd need to include those rules if you were not allowed to quote attribute values.
With the code I posted above, the only reason I used 2 single quotes is that it is how SQL handles putting a single quote inside of a string. If I just used 1 single quote, it would see that the \"P\" section was outside the string and complain. Putting in 2 single quotes beside each other in TSQL will put in 1 single quote inside the string. For example:
SELECT 'It's FRIDAY!!!'
will give you an error but:
SELECT 'It''s FRIDAY!!!'
will give you a single column, single row table with the text "It's FRIDAY!!!" in it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy