Link to home
Create AccountLog in
Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on

Query xml data converted to nvarchar in sql server

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>

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

What version of SQL Server are you using?
try something like this:

DECLARE @XML XML
    
SET @XML = CONVERT (XML, '<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>')

SELECT
c.value('@rootPath', 'VARCHAR(250)') rootPath,
c.value('@PDFFile', 'VARCHAR(250)') PDFFile
FROM
@XML.nodes('FormsXML/printJob/ExportDocument') t(c)

Open in new window

USE [testDB1]
GO
CREATE TABLE ExportDocument (rootPath varchar(300) , PDFFile varchar(300));  
GO  
DECLARE @docHandle int;  
DECLARE @xmlDocument NVarChar(Max);  
SET @xmlDocument = N'<FormsXML> and the rest of your XML </FormsXML>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;  
-- INSERT ExportDocument   -- Commented for Debug
SELECT *  
FROM OPENXML(@docHandle, N'/FormsXML/printJob/ExportDocument')  
  WITH ExportDocument;  
EXEC sp_xml_removedocument @docHandle;

User generated image

And now the cycle through the [history] table:
DECLARE cr CURSOR READ_ONLY FAST_FORWARD FOR SELECT xmlField FROM history;
OPEN cr;
WHILE 1=1 BEGIN
   FETCH NEXT FROM cr INTO @xmlDocument
   IF @@FETCH_STATUS <> 0 BREAK; -- End of cycle
   EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;  
   INSERT ExportDocument
   SELECT * FROM OPENXML(@docHandle, N'/FormsXML/printJob/ExportDocument')
   WITH ExportDocument;  
   EXEC sp_xml_removedocument @docHandle;
END
CLOSE cr;
DEALLOCATE cr;

SELECT TOP(100) FROM ExportDocument; -- to see the result
Avatar of johnnyg123

ASKER

Thanks for all the replies!

I am currently evaluating them

I am using sql 2014
Hi Ryan

If I do a selection of the XML I get the following

<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>

At the very end

there is this tag

 <dataElement name="POLDADDR2" value="City, IL, 60602" itemID="d5D81E9926A85437B91972B97639A65FD" parentID="7" />

Is there a way to get the value of POLDAADDR2 returned ?

City, IL, 60602


ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account