May 5, 2014 at 3:34 am
Hi friends,
declare @xml table (xmldata xml)
insert @xml select
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>
'
i want to extract data in in table format
ParseObjectName FieldType FieldSizeGrammar
Motel VehicleNo Int NULL div.biz-page-subheader li > span.i-phone-biz_details-wrap
mapbox-icon span.biz-partno[/size]
can any body help me.
Regards
Siva
May 5, 2014 at 4:19 am
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
May 5, 2014 at 4:42 am
Thank you Eirikur Eiriksson.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply