MSSQL Number from XML (varchar) cells

Question

Can you tell me how to parse XML not only correctly, but beautifully, considering that the cell in nvarchar(max) is the number between two tags?

Example (XML):

<SignedLicenseInfo> <LicenseInfo> <ExpirationDate> 9223372036854775807 </ ExpirationDate> <AvailiableModules> 30 </ AvailiableModules> <PagesLimit> 708,976 </ PagesLimit> <PagesLimitRenewDate> 132274944000000000 </ PagesLimitRenewDate> <IsDefaultLicense> true </ IsDefaultLicense> <CustomModuleAuthorId> </ CustomModuleAuthorId> <IsCountRecognizedPagesOnly> false </ IsCountRecognizedPagesOnly> <IsCyrillicLanguageEnabled> true </ IsCyrillicLanguageEnabled> <MaximumNumberOfTemplateFields> 2147483647 </ MaximumNumberOfTemplateFields> <Type> 11 </ Type> <Amount> 0 </ Amount> </ StationInfo > </ LicenseStationInfo> <CustomFeatures> </ CustomFeatures> <LocalExpirationDate> 132271122849228006 </ LocalExpirationDate> </ LicenseInfo> <Signature> XXXXXXXXXXXXXYYYYYYYYYYYYYYYYYYYYYYYYXXXXXXXXXdfsd </ Signature> </ SignedLicenseInfo>

You need to get what is in (708976). I got the following query in the database:

SELECT substring (s.Value, CHARINDEX ('<PagesLimit>', s.Value) +12, CHARINDEX ('</PagesLimit>', s.Value, CHARINDEX ('<PagesLimit>', s.Value) +12) - CHARINDEX ('<PagesLimit>', s.Value) -12) as My_String
from dbo.Settings s

How can it be made more concise and logical? And not by selecting the number of characters to and fro. Thanks.

Answer 1

Your XML is not well-formed... I had to remove some blanks and there are two closing tags, which are missing the opening tags (find them commented).

XML is not to be parsed as a string with some fancy extras. There are some native XML-methods using XQuery and XPath.

Try this:

DECLARE @xml XML=
N'<SignedLicenseInfo>
  <LicenseInfo>
    <ExpirationDate> 9223372036854775807 </ExpirationDate>
    <AvailiableModules> 30 </AvailiableModules>
    <PagesLimit> 708,976 </PagesLimit>
    <PagesLimitRenewDate> 132274944000000000 </PagesLimitRenewDate>
    <IsDefaultLicense> true </IsDefaultLicense>
    <CustomModuleAuthorId />
    <IsCountRecognizedPagesOnly> false </IsCountRecognizedPagesOnly>
    <IsCyrillicLanguageEnabled> true </IsCyrillicLanguageEnabled>
    <MaximumNumberOfTemplateFields> 2147483647 </MaximumNumberOfTemplateFields>
    <Type> 11 </Type>
    <Amount> 0 </Amount>
    <!-- /StationInfo -->
    <!-- /LicenseStationInfo -->
    <CustomFeatures />
    <LocalExpirationDate> 132271122849228006 </LocalExpirationDate>
  </LicenseInfo>
  <Signature> XXXXXXXXXXXXXYYYYYYYYYYYYYYYYYYYYYYYYXXXXXXXXXdfsd </Signature>
</SignedLicenseInfo>';

SELECT @xml.value('(/SignedLicenseInfo/LicenseInfo/PagesLimit/text())[1]','varchar(max)');

This returns 708,976.

Answer 2

Thanks to all. Using the information provided, I did this:

declare @xml xml;
SELECT @xml = s.Value from dbo.Settings s where s.Name='LicenseString';
select @xml.value('(/SignedLicenseInfo/LicenseInfo/PagesLimit)[1]', 'int');