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: Wednesday, August 6, 2014 1:06 AM
Points: 60, Visits: 519
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:19 PM
Points: 1,575, Visits: 4,320
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: Wednesday, August 6, 2014 1:06 AM
Points: 60, Visits: 519
Thank you Eirikur Eiriksson.
Post #1567463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse