Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parse XML Data to Table format Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 3:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 24, 2015 4:22 AM
Points: 67, Visits: 532
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

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[/size]


can any body help me.
Regards
Siva
Post #1567449
Posted Monday, May 5, 2014 4:19 AM This worked for the OP Answer marked as solution
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 3,489, Visits: 9,335
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
Post #1567460
Posted Monday, May 5, 2014 4:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 24, 2015 4:22 AM
Points: 67, Visits: 532
Thank you Eirikur Eiriksson.
Post #1567463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse