troubleshooting Question

Query xml data converted to nvarchar in sql server

Avatar of johnnyg123
johnnyg123Flag for United States of America asked on
SQLXMLMicrosoft SQL Server
6 Comments1 Solution19 ViewsLast Modified:
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>

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros