SQL query with XML parameter

Question

EDIT: I have found a relevant answer already on stack overflow here: XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

I have not dealt with XML in T-SQL before, and I am modifying an existing legacy stored proc, and picking most if it up through trial and error.

however I have hit a problem where trial and error is proving fruitless, and very slow. Think it's time to appeal to stack overflow gurus!

Here is some XML


    <?xml version=\"1.0\"?>
    <Notification xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">
    <NotificationId>0</NotificationId>
    <UserNotifications>
    <UserNotification>
    <UserNotificationId>0</UserNotificationId>
    <NotificationId>0</NotificationId>
    <UserId>13514</UserId>
    <MessageTypeId>1</MessageTypeId>
    </UserNotification>
    <UserNotification>
    <UserNotificationId>0</UserNotificationId>
    <NotificationId>0</NotificationId>
    <UserId>13514</UserId>
    <MessageTypeId>2</MessageTypeId>
    </UserNotification>
    </UserNotifications>
    </Notification>

The Stored Proc in question accepts the above XML as a parameter:


    CREATE PROCEDURE [dbo].[Notification_Insert] 
        @ParametersXml XML
    AS
    BEGIN

The XML contains child "UserNotification" elements. I would like to select the UserId, MessageTypeId of each UserNotification, into a table like this


    UserId | MessageTypeId
    13514 | 1
    13514 | 2

Obviously the size of the collection is not fixed.

My current attempt (which doesn't work - is along these lines:


    DECLARE @UserDetails TABLE ( UserId INT, MessageTypeId INT);
                INSERT INTO @UserDetails (UserId, MessageTypeId)
                SELECT Tab.Col.value('@UserId','INT'),
    Tab.Col.value('@MessageTypeId','INT')
                FROM     @ParametersXml.nodes('/Notification/UserNotifications[not(@xsi:nil = "true")][1]/UserNotification') AS Tab(Col)

But this never inserts anything..

I have been playing around with this for a while now and not had any joy :(

score 0 · Answer 1 · answered Sep 18 '15 at 12:57

0

I would suggest going through the links below. I found them short and quick to go through:

http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/ http://blog.sqlauthority.com/2009/02/13/sql-server-simple-example-of-reading-xml-file-using-t-sql/

answered Sep 18 '15 at 12:57

Unbound

177
2
14

Thanks but I have just stumbled accross a more concise answer to my problem here on StackOverflow: http://stackoverflow.com/questions/19940566/xquery-value-value-requires-a-singleton-or-empty-sequence-found-oper – Darrell Sep 18 '15 at 13:10

score 0 · Accepted Answer · edited May 23 '17 at 10:27

I found the solution to this problem through further searching stack overflow.

The query I need (thanks to XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *')


INSERT INTO @UserDetails (UserId, MessageTypeId)
            SELECT UserNotification.value('UserId[1]','INT'), 
            UserNotification.value('MessageTypeId[1]','INT')
            FROM @ParametersXml.nodes('//Notification/UserNotifications') AS x(Coll)
            cross apply @ParametersXml.nodes('//Notification/UserNotifications/UserNotification') as un(UserNotification)

SQL query with XML parameter

2 Answers2