I'm trying to convert my XML string into some SQL elements
unfortunately i have some modification problems
my SQL string :
select @xml
--, @XML.query('Document/Content/Truncated') ff
-- URL --
, @xml.value('(Document/Metadata/Fields/docid)[1]', 'varchar(100)') D_URL
-- title --
, @xml.value('(Document/Metadata/Fields/title)[1]', 'varchar(100)') Title
-- name --
, @xml.value('(Document/Metadata/Facets/Facet/_ne/person)[1]', 'varchar(100)') MD_Name
-- address --
, left((substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Location & Contact%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 550)),
CHARINDEX ('[MAP]' , (substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Location & Contact%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 550)) ))Office_Location
-- gender--
, substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Years in Practice%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 10) Gender
-- phone --
,right(left((substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Location & Contact%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 550)),
CHARINDEX ('Phone Number' , (substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Location & Contact%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 550)) )),
CHARINDEX ('(' , (substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Location & Contact%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 550)) ))Phone
-- speciality --
, substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Specialties & Qualifications%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 100) Specialities
-- type--
, substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Doctor Type%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 15) Doctor_Type
-- Explenation --
, substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Specialty:%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 400) Explenation
-- certification --
, substring(@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') , 18+patindex('%Certifications & Licensure%', (@xml.value('(Document/Content/Text)[1]', 'nvarchar(4000)') )), 200) Board_Certifications
I have a problem ouput in:
- output: i just can't catch the damn phone (+332) ....
- Specialty: i can't cut all the sentence
any suggestions ?
