I have inherited a vendor data base with a table named history with a column named xmldata that is defined as nvarchar(max) the data in it appears to be xml data as it has random html tags. some appear to be parent html tags.
I need to write a query that returns the rootPath and PDFFile values in separate fields but not sure how to do this
Please see sample column data below
<FormsXML>
<printJob name="Bind"><ExportDocument docType="PDF" rootPath="F:\Creek\Policy\Forms\" PDFFile="C:\Users\app.pool\AppData\Local\Temp\New Business Bind HSP000000001 10_19_2020 114706.630" OnePDF="1" duplexPrint="False" policyForms="1">
<ExportSection name="IE-0001_PPL" value="Carrier_Indemnity_MPL_Forms_Multistate_1_0_0_0" caption="SERVICE OF SUIT ENDORSEMENT" paperBinNum="0" category="" printDefault="Mandatory" topicRef="" pageRef="" printed="1"><group name="data" startIteration="1" endIteration="1" itemID="d5D81E9926A85437B91972B97639A65FD" /><subForm name="IE-0001_PPL SERVICE OF SUIT ENDORSEMENT.doc" path="MPL|Indemnity|" /><dataElement name="POLNUM" value="HSP000000001" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="EFFDT" value="11/01/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLNAME" value="Test Hospital Policy" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLDADDR1" value="20 N. Michigan" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLDADDR2" value="Chicago, IL, 60602" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="Today" value="10/19/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /></ExportSection>
<ExportSection name="IE-0003" value="Carrier_Indemnity_MPL_Forms_Multistate_1_0_0_0" caption="APPLICABLE LAW ENDORSEMENT" paperBinNum="0" category="" printDefault="Mandatory" topicRef="" pageRef="" printed="1"><group name="data" startIteration="1" endIteration="1" itemID="d5D81E9926A85437B91972B97639A65FD" /><subForm name="IE-0003 APPLICABLE LAW ENDORSEMENT.doc" path="MPL|Indemnity|" /><dataElement name="POLNUM" value="HSP000000001" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="EFFDT" value="11/01/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="TODAY" value="10/19/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLNAME" value="Test Hospital Policy" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLDADDR1" value="20 N. Michigan" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLDADDR2" value="Chicago, IL, 60602" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="IssueDate" value="10/19/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /></ExportSection><ExportSection name="IE-0004" value="Carrier_Indemnity_MPL_Forms_Multistate_1_0_0_0" caption="GOVERNMENT ACCESS TO RECORDS ENDORSEMENT" paperBinNum="0" category="" printDefault="Mandatory" topicRef="" pageRef="" printed="1"><group name="data" startIteration="1" endIteration="1" itemID="d5D81E9926A85437B91972B97639A65FD" /><subForm name="IE-0004.doc" path="MPL|Indemnity|" /><dataElement name="PolNum" value="HSP000000001" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="EffDt" value="11/01/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="Today" value="10/19/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="PolName" value="Test Hospital Policy" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="PolAddr1" value="20 N. Michigan" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="PolAddr2" value="Chicago, IL, 60602" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /></ExportSection><ExportSection name="IE-0002" value="Carrier_Indemnity_MPL_Forms_Multistate_1_0_0_0" caption="MINIMUM EARNED PREMIUM ENDORSEMENT" paperBinNum="0" category="" printDefault="Mandatory" topicRef="" pageRef="" printed="1"><group name="data" startIteration="1" endIteration="1" itemID="d5D81E9926A85437B91972B97639A65FD" /><subForm name="IE-0002 MINIMUM EARNED PREMIUM ENDORSEMENT.doc" path="MPL|Indemnity|" /><dataElement name="POLNUM" value="HSP000000001" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="EFFDT" value="11/01/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="TODAY" value="10/19/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLNAME" value="Test Hospital Policy" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLDADDR1" value="20 N. Michigan" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLDADDR3" value="Chicago, IL, 60602" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /></ExportSection><ExportSection name="IE-0031" value="Carrier_Indemnity_MPL_Forms_Multistate_1_0_0_0" caption="STAFFING SERVICES EXCLUSION" paperBinNum="0" category="" printDefault="Selected" topicRef="" pageRef="" printed="1"><group name="data" startIteration="1" endIteration="1" itemID="d5D81E9926A85437B91972B97639A65FD" /><subForm name="IE-0031.doc" path="MPL|Indemnity|" /><dataElement name="EffDate" value="11/01/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="AccountName" value="Test Hospital Policy" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="Add1" value="20 N. Michigan" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="Add2" value="Chicago, IL, 60602" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLNUM" value="HSP000000001" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="TODAY" value="10/19/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /></ExportSection><ExportSection name="IE-0001" value="Carrier_Indemnity_MPL_Forms_Multistate_1_0_0_0" caption="SERVICE OF SUIT ENDORSEMENT" paperBinNum="0" category="" printDefault="Mandatory" topicRef="" pageRef="" printed="1"><group name="data" startIteration="1" endIteration="1" itemID="d5D81E9926A85437B91972B97639A65FD" /><subForm name="IE-0001 SERVICE OF SUIT ENDORSEMENT.doc" path="MPL|Indemnity|" /><dataElement name="PolicyNumber" value="HSP000000001" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="EffectiveDate" value="11/01/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="Today" value="10/19/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="ApplicantName" value="Test Hospital Policy" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="Add1" value="20 N. Michigan" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="Add2" value="Chicago, IL, 60602" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /></ExportSection><ExportSection name="IE-0011" value="Carrier_Indemnity_MPL_Forms_Multistate_1_0_0_0" caption="LIMITS OF INSURANCE PER NAMED INSURED ENDORSEMENT" paperBinNum="0" category="" printDefault="Mandatory" topicRef="" pageRef="" printed="1"><group name="data" startIteration="1" endIteration="1" itemID="d5D81E9926A85437B91972B97639A65FD" /><subForm name="IE-0011 - Header.doc" path="MPL|Indemnity|IE-0011|" /><subForm name="IE-0011 - Corp.doc" path="MPL|Indemnity|IE-0011|"><group name="Risk" startIteration="1" endIteration="1" itemID="r7A42954FCDEA487AA13B7A6F7BF352D9" /><group name="CovPrimaryLiability" startIteration="1" endIteration="1" itemID="cF04178F583264FEA981ED7C962A3F430" /><dataElement name="INSURED" value="Test Hospital Policy" itemID="r7A42954FCDEA487AA13B7A6F7BF352D9" parentID="l3F6BF453932D466EAFB4CB1C692BE41F" /><dataElement name="EFFDATE" value="11/01/2020" itemID="cF04178F583264FEA981ED7C962A3F430" parentID="r7A42954FCDEA487AA13B7A6F7BF352D9" /><dataElement name="ENDDATE" value="" itemID="cF04178F583264FEA981ED7C962A3F430" parentID="r7A42954FCDEA487AA13B7A6F7BF352D9" /><dataElement name="RETRODATE" value="11/01/2020" itemID="cF04178F583264FEA981ED7C962A3F430" parentID="r7A42954FCDEA487AA13B7A6F7BF352D9" /><dataElement name="EachLimit" value="$1,000,000" itemID="cF04178F583264FEA981ED7C962A3F430" parentID="r7A42954FCDEA487AA13B7A6F7BF352D9" /><dataElement name="AggLimit" value="$1,000,000" itemID="cF04178F583264FEA981ED7C962A3F430" parentID="r7A42954FCDEA487AA13B7A6F7BF352D9" /></subForm><subForm name="IE-0011 - Footer.doc" path="MPL|Indemnity|IE-0011|" /><dataElement name="POLNUM" value="HSP000000001" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="EFFDT" value="11/01/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="TODAY" value="10/19/2020" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLNAME" value="Test Hospital Policy" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLDADDR1" value="20 N. Mic" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /><dataElement name="POLDADDR2" value="City, IL, 60602" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" /></ExportSection><ControlBlock /></ExportDocument></printJob>
</FormsXML>
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.