XML query returning NULL values for the given query

_{Monday, August 12, 2013 4:30 AM}

Hi All,

I have an XML field in the  table like this and I am trying to read the values using the following query.

But the query returns NULL across the records. Please let me know where I am going wrong.

<root xmlns:i="http://www.w3.org/2001/XMLSchema-instance">  <Header xmlns="http://schemas.datacontract.org/2004/07/ABC.Fabric.Services.Messages">    <UserDeviceId>1</UserDeviceId>    <CorrelationId>71fb0504-d58f-4640-aecc-912100f7e85e</CorrelationId>    <DeviceId>SLATE_1</DeviceId>    <SentDateTime>2012-11-09T15:29:15.6040269+11:00</SentDateTime>    <ShiftId>1</ShiftId>    <UserName>UATUser</UserName>  </Header></root>

SELECT XMLMessage,XMLMessage.value('(./UserDeviceId/node())[1]', 'int') as CopsDeviceId,XMLMessage.value('(./CorrelationId/node())[1]', 'NVARCHAR(MAX)') as CorrelationId,XMLMessage.value('(./DeviceId/node())[1]', 'int') as DeviceId,XMLMessage.value('(./SentDateTime/node())[1]', 'DATETIME') as SentDateTime,XMLMessage.value('(./ShiftId/node())[1]', 'int') as ShiftId,XMLMessage.value('(./UserName/node())[1]', 'NVARCHAR(MAX)') as UserNameFROM rollingLog

Thanks
M.Mahendra

Please click the 'Mark as Answer' button if my Reply helped you to solve your problem! Thanks M.Mahendra

_{Monday, August 12, 2013 5:51 AM ✅Answered}

I think, your XML has some issues:

 <Header xmlns:i="http://schemas.datacontract.org/2004/07/ABC.Fabric.Services.Messages">

Try the below:

 Declare @xml XML = '<root xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <Header xmlns:i="http://schemas.datacontract.org/2004/07/ABC.Fabric.Services.Messages">
    <UserDeviceId>1</UserDeviceId>
    <CorrelationId>71fb0504-d58f-4640-aecc-912100f7e85e</CorrelationId>
    <DeviceId>SLATE_1</DeviceId>
    <SentDateTime>2012-11-09T15:29:15.6040269+11:00</SentDateTime>
    <ShiftId>1</ShiftId>
    <UserName>UATUser</UserName>
  </Header>
</root>'

SELECT i.c.value('.', 'varchar(max)')
FROM @xml.nodes('/root/Header/CorrelationId') i(c)

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Monday, August 12, 2013 6:23 AM}

If possible remove the namespaces from "root" and "Header" tag and try it once.

<root>

<Header>

<UserDeviceId>1</UserDeviceId>

<CorrelationId>71fb0504-d58f-4640-aecc-912100f7e85e</CorrelationId>

<DeviceId>SLATE_1</DeviceId>

<SentDateTime>2012-11-09T15:29:15.6040269+11:00</SentDateTime>

<ShiftId>1</ShiftId>

<UserName>UATUser</UserName>

</Header>

</root>

Regards, RSingh

_{Monday, August 12, 2013 6:32 AM}

your datattype in bellow code must be nvarchar(max):

XMLMessage.value('(./DeviceId/node())[1]', 'int') as DeviceId,

another approach is:

DECLARE @sql XML
SET @sql = 
N'<root xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Header xmlns="http://schemas.datacontract.org/2004/07/ABC.Fabric.Services.Messages">
        <UserDeviceId>1</UserDeviceId>
        <CorrelationId>71fb0504-d58f-4640-aecc-912100f7e85e</CorrelationId>
        <DeviceId>SLATE_1</DeviceId>
        <SentDateTime>2012-11-09T15:29:15.6040269+11:00</SentDateTime>
        <ShiftId>1</ShiftId>
        <UserName>UATUser</UserName>
    </Header>
</root>'


SELECT @sql = REPLACE(CAST(@sql AS NVARCHAR(max)), 'Header xmlns="http://schemas.datacontract.org/2004/07/ABC.Fabric.Services.Messages"', 'Header')

SELECT 
 T.Header.query('UserDeviceId').value('.', 'INT') AS customer_id
 ,T.Header.query('CorrelationId').value('.', 'VARCHAR(max)') AS customer_name
, T.Header.query('DeviceId').value('.', 'NVARCHAR(MAX)') AS customer_id
 ,T.Header.query('SentDateTime').value('.', 'DATETIME') AS faxr_name
, T.Header.query('ShiftId').value('.', 'INT') AS customer_id
 ,T.Header.query('UserName').value('.', 'VARCHAR(max)') AS customer_name
FROM @sql.nodes('root/Header') AS T(Header);

If This post is helpful post, please vote it. If This post is the answer to your question, please Propose it as answer. Thanks so much for your feedback.