Trying to extract part of a string, but the field is xml data type. I just need the mac address from this field:

Question

question

GreMoe asked Aug 16, '21

Trying to extract part of a string, but the field is xml data type. I just need the mac address from this field:

I've tried the following:

SELECT
[Number]
,[Description]
,[Configuration]
-- ,CAST(substring([Configuration], patindex('macAddress=',[Configuration])+2, 12) as nvarchar(max)) as MAC
--,substring([Configuration], patindex('macAddress=',cast ([Configuration])+2, 12)as unsigned) MAC
-- ,substring([Configuration], patindex('%macAddress=%',[Configuration])+2, 12) MAC
FROM [APIHealthcare_Test].[dbo].[Device]

This line: substring([Configuration], patindex('%macAddress=%',[Configuration])+2, 12) MAC returns an "Argument data type xml is invalid for argument 2 of patindex function." error so I know I need to use Cast, just can't figure out the syntax.

Very new to sql.

Can someone help?

substring sql xml cast

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GreMoe commented · Aug 16, 2021 at 07:24 PM

Looking for output similar to below:

Number Description MAC

101 Time clock by Elevator 000BAB2230DA

102 Time clock by Rear Stair 000BAB2235EF

0 ·

Answer 1 · 2021-08-17T07:31:43Z

Kev Riley answered Aug 17, '21

If the column is an XML datatype then you can use the xml data type methods (https://docs.microsoft.com/en-us/sql/t-sql/xml/xml-data-type-methods) instead of trying to do string manipulation. Try this:

select 
 [Number]
,[Description]
,[Configuration]
[Configuration].value('DeviceSettings[1]/Hardware[1]/IP[1]/@macAddress','varchar(50)')
FROM [APIHealthcare_Test].[dbo].[Device]

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

GreMoe commented · Aug 17, 2021 at 01:16 PM

Your suggestions work perfectly!! Thank you very much.

0 ·

question