Try this for size
😎
declare @xml table (xmldata xml)
insert INTO @xml (xmldata) VALUES(
N'<parseObject name="Motel">
<fields>
<field name="vehicleno" fieldType="int" fieldSize="">
<grammar>
<data><![CDATA[
div.biz-page-subheader li > span.i-phone-biz_details-wrap mapbox-icon span.biz-partno
]]></data>
<attribute>html</attribute>
</grammar>
</field>
</fields>
</parseObject>
')
/*----------------*/
SELECT
RO.OT.value('@name','NVARCHAR(256)') AS [ParseObject]
,FIE.LD.value('@name','NVARCHAR(256)') AS [Name]
,FIE.LD.value('@fieldType','NVARCHAR(256)') AS [FieldType]
,NULLIF(FIE.LD.value('@fieldSize','INT'),0) AS [FieldSize]
,DA.TA.value('.[1]','NVARCHAR(MAX)') AS [Grammar]
FROM @xml X
OUTER APPLY X.xmldata.nodes('parseObject') AS RO(OT)
OUTER APPLY RO.OT.nodes('fields/field') AS FIE(LD)
OUTER APPLY FIE.LD.nodes('grammar/data') AS DA(TA)
Results
ParseObject Name FieldType FieldSize Grammar
------------ ---------- ---------- ----------- -------------------------------------------------------------------------------------
Motel vehicleno int NULL div.biz-page-subheader li > span.i-phone-biz_details-wrap mapbox-icon span.biz-partno