question

GreMoe avatar image
GreMoe asked

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

<DeviceSettings environment="Live"><Hardware><IP connectionType="2" hostName="apick101.illini.genesishealth.com" port="600" protocolType="2" servicePort="10101" macAddress="000BAB2230DA" subnetMask="255.255.255.0" gatewayIPAddress="172.31.12.1" /></Hardware><Software terminalNumber="101" commandTimeout="600" numberOfRetries="5" maximumErrors="300" pollInterval="60" isStartTimeRequired="true" reconnectMinutes="30"><Deduction deductionOfEarningsID="0" maximumBadBadgeSize="0" maximumLogSize="0" isMobile="False" is24HourTime="false" communicationTimeout="4" pinRetries="3" /><WebClient userName="apitech" password="ugVzFTabyWC5tSTiTrE7ng==" useProxy="false" proxyHost="" proxyUserName="" proxyPassword="" proxyPort="0" /></Software></DeviceSettings>


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?

substringsql xmlcast
1 comment
10 |1200 characters needed characters left characters exceeded

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

Looking for output similar to below:

Number Description MAC

101 Time clock by Elevator 000BAB2230DA

102 Time clock by Rear Stair 000BAB2235EF

0 Likes 0 ·

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered

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 comment
10 |1200 characters needed characters left characters exceeded

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

Your suggestions work perfectly!! Thank you very much.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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