Int Placeholder %d in xp_sprintf - SQL Server

Question

I'm having a XML Template I need to construct a XML based on row values in a Table using xp_sprintf. Moreover the table has an Int and Bit values.

Table schema: StudentMark:

CREATE TABLE [dbo].[StudentMark]
(
    [StudentMarkId] [int] IDENTITY(1,1) NOT NULL,
    [StudentId] [uniqueidentifier] NOT NULL,
    [SubjectId] [uniqueidentifier] NOT NULL,
    [Score] [int] NOT NULL,
    [ScoreInfo] [xml] NOT NULL,
    [GeneratedOn] [datetime2](2) NOT NULL,
    [IsPass] [bit] NOT NULL,
    CONSTRAINT [PK_StudentMark] 
       PRIMARY KEY CLUSTERED ([StudentMarkId] ASC)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Sample seed data

INSERT INTO [dbo].[StudentMark] ([StudentId], [SubjectId], [ScoreInfo], GeneratedOn], [Score], [IsPass])
VALUES ('FC3CB475-B480-4129-9190-6DE880E2D581', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:10:15', 95, 1),
       ('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', '0D72F79E-FB48-4D3E-9906-B78A9D105081', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-10 10:10:15', 100, 1),
       ('0F4EF48C-93E3-41AA-8295-F6B0E8D8C3A2', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20', 25, 0),
       ('FC3CB475-B480-4129-9190-6DE880E2D581', 'AB172272-D2E9-49E1-8040-6117BB6743DB', '<StudentMarkAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"></StudentMarkAttribute>', '2017-08-16 09:06:20', 82, 1);

Requirement: I need to Convert the Row as a following XML and need to update in the appropriate record's column [dbo].[StudentMark].[ScoreInfo].

XML Template:

<ScoreInfo>
    <StudentMarkId>%d</StudentMarkId>
    <StudentId>%s</StudentId>
    <SubjectId>%s</SubjectId>
    <Score>%d</Score>
    <GeneratedOn>%s</GeneratedOn>
    <IsPass>%d</IsPass>
</ScoreInfo>

I tried the following sample code from one of the Stackoverflow question

declare @name varchar(150)
set @name = 'John'

declare @score int
set @score = 75

DECLARE @ret varchar(500)
exec master..xp_sprintf @ret OUTPUT, 'Hello %s, your score is %d', @name, @score

PRINT @ret

I'm getting the following error message

Error executing extended stored procedure: Invalid Parameter Type Msg 50003, Level 1, State 0

Kindly assist me how to construct the Template XML with the specified table [dbo].[StudentMark]

I reffered the following Questions

Kindly assist me how to use the Format Specifier of digits, etc.,

Do you have any alternate function for this ? If yes please assist me... — B.Balamanigandan, Aug 22 '17 at 18:07
You could start by reading [FOR XML (SQL Server)](https://learn.microsoft.com/en-us/sql/relational-databases/xml/for-xml-sql-server) — James Z, Aug 22 '17 at 18:09
@JamesZ - Ok Thanks for your assistance, but in this `xp_sprintf` how could we use `%d` ? could you please clarify it... — B.Balamanigandan, Aug 22 '17 at 18:22
you cannot use %d, xp_sprintf only supports %s. You'll have to manually convert @score to a string type, then pass it in to xp_sprintf along with format string 'Hello %s, your score is %s'. — Xingzhou Liu, Aug 22 '17 at 19:06
There is a [format function](https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql), but it uses .NET format strings. — Xingzhou Liu, Aug 22 '17 at 19:09
@B.Balamanigandan So, you said to me that you need to use xp_sprintf, but then approved answer that doesn't use that? — James Z, Aug 23 '17 at 04:00
@JamesZ - Yes you are right. I re-marked the answer... Put your explanation in the answer block, I mark your answer is right... — B.Balamanigandan, Aug 23 '17 at 04:54
Just accept that answer, but please fix your question, since it has nothing to do with xp_sprintf, or at least anyone searching that will not really find answers here — James Z, Aug 23 '17 at 14:33

Xingzhou Liu · Answer 1 · 2017-08-23T05:05:02.673

xp_sprintf only supports string arguments and %s placeholders, so there is no way to use %d placeholder in SQL Server. You would have to cast the number to a string, then use it to fill a %s placeholder.

The easiest way to satisfy your requirement is to use built in XML functionality.

update [target]
    SET [ScoreInfo] = [XmlValue]
    FROM [dbo].[StudentMark] AS target 
    JOIN (
             SELECT [StudentMarkId],
                (
                   SELECT 
                      [StudentMarkId],[StudentId], [SubjectId], [GeneratedOn], [Score], [IsPass] 
                    FROM [dbo].[StudentMark] AS innr 
                    WHERE outr.[StudentMarkId] = innr.[StudentMarkId]
                    FOR XML PATH('ScoreInfo'), TYPE
                 ) as [XmlValue]
             FROM [dbo].[StudentMark] AS outr
         ) AS source 
     ON target.[StudentMarkId] = source.[StudentMarkId]

will set ScoreInfo equal to:

 <ScoreInfo>
    <StudentMarkId>%d</StudentMarkId>
    <StudentId>%s</StudentId>
    <SubjectId>%s</SubjectId>
    <Score>%d</Score>
    <GeneratedOn>%s</GeneratedOn>
    <IsPass>%d</IsPass>
 </ScoreInfo>

for every row, in a single query.

Yours is partial answer of my requirement, but my question is how to use `%d` in `xp_sprintf`. — B.Balamanigandan, Aug 23 '17 at 04:55

score 0 · Answer 2 · answered Apr 25 '19 at 16:40

Because xp_sprintf only supports "%s" you need to CAST any numeric values to a VARCHAR

Modify your example as follows:

DECLARE @ret         VARCHAR(500)
DECLARE @name    VARCHAR(150)
DECLARE @val         VARCHAR(10)
DECLARE @score    INT

set @name = 'John'
set @score = 75
set @val = CAST(@score as VARCHAR(10))

EXEC master..xp_sprintf @ret OUTPUT, 'Hello %s, your score is %s', @name, @val

PRINT @ret

Int Placeholder %d in xp_sprintf - SQL Server

2 Answers2