Xpath in nested xml is not working in postgre

bill-ramos-rmoswi / babelfish_postgresql_towel

Bill Ramos's knowledge base for tips, tricks, and best practices for migrating SQL Server solutions to Babelfish for PostgreSQL and the open-source WiltonDB for Windows

Apache License 2.0

1 stars 1 forks source link

Xpath in nested xml is not working in postgre #18

Open JayakumarEMIS opened 1 month ago

JayakumarEMIS commented 1 month ago

Hi @bill-ramos-rmoswi,

In xml, I have two different DispensedDrugDescription value for common Code, so in sql we already done using the cross apply xml nodes to get value based DispensedDrugDescription value for common code, but in postgre and babelfish i am not getting reference, even though i tried unnest xpath in babelfish but not able to get the result, i have shared both the query of sql and postgre but getting different kindly share your knowledge to get the exact result in babelfish, and the xpath should be same in sql i want to get result without change the xpath in babelfish. I attached the script both sql and postgre. XPath.zip

staticlibs commented 1 month ago

@JayakumarEMIS

You are using the string function in the xpath expression, so getting only first value from the array:

If the object is a node-set, the string value of the first node in the set is returned.

Try something like this instead:

WITH xml_data AS (
SELECT '
<PORX_IN090101UK31 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema"
                   xmlns="urn:hl7-org:v3">
    <ControlActEvent classCode="CACT" moodCode="EVN">
        <subject typeCode="SUBJ" contextConductionInd="false">
            <DispenseClaim classCode="INFO" moodCode="EVN">
                <pertinentInformation1 typeCode="PERT" contextConductionInd="true">
                    <pertinentSupplyHeader classCode="SBADM" moodCode="EVN">
                        <legalAuthenticator typeCode="LA" contextControlCode="OP">
                            <AgentPerson classCode="AGNT">
                                <representedOrganization classCode="ORG" determinerCode="INSTANCE">
                                    <id root="1.2.826.0.1285.0.1.10" extension="Test"/>
                                </representedOrganization>
                            </AgentPerson>
                        </legalAuthenticator>
                        <pertinentInformation1 typeCode="PERT" inversionInd="false" contextConductionInd="true"
                                               negationInd="false">
                            <pertinentSuppliedLineItem classCode="SBADM" moodCode="PRMS">
                                <component typeCode="COMP">
                                    <suppliedLineItemQuantity classCode="SPLY" moodCode="EVN">
                                        <product typeCode="PRD" contextControlCode="OP">
                                            <suppliedManufacturedProduct classCode="MANU">
                                                <manufacturedSuppliedMaterial classCode="MMAT"
                                                                              determinerCode="INSTANCE">
                                                    <code code="9516211000001100"
                                                          codeSystem="2.16.840.1.113883.2.1.3.2.4.15"
                                                          displayName="Volumatic (GlaxoSmithKline UK Ltd) 1 device"/>
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                        <pertinentInformation1 typeCode="PERT" inversionInd="false" contextConductionInd="true"
                                               negationInd="false">
                            <pertinentSuppliedLineItem classCode="SBADM" moodCode="PRMS">
                                <component typeCode="COMP">
                                    <seperatableInd value="false"/>
                                    <suppliedLineItemQuantity classCode="SPLY" moodCode="EVN">
                                        <product typeCode="PRD" contextControlCode="OP">
                                            <suppliedManufacturedProduct classCode="MANU">
                                                <manufacturedSuppliedMaterial classCode="MMAT"
                                                                              determinerCode="INSTANCE">
                                                    <code code="2489911000001104"
                                                          codeSystem="2.16.840.1.113883.2.1.3.2.4.15"
                                                          displayName="Vitrex Soft lancets 0.65mm/23gauge (Vitrex Medical Ltd) 100 lancet"/>
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                    </pertinentSupplyHeader>
                </pertinentInformation1>
            </DispenseClaim>
        </subject>
    </ControlActEvent>
</PORX_IN090101UK31>
'::xml AS xml_content)
SELECT 
unnest(xpath('//../../../p:legalAuthenticator[1]/p:AgentPerson[1]/p:representedOrganization[1]/p:id[1]/@extension', xml_content, ARRAY[ARRAY['p', 'urn:hl7-org:v3']])::text[]) Code,
unnest(xpath('//p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@displayName', xml_content, ARRAY[ARRAY['p', 'urn:hl7-org:v3']])::text[]) DispensedDrugDescription,
unnest(xpath('//p:pertinentSuppliedLineItem[1]/p:component[1]/p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@code', xml_content, ARRAY[ARRAY['p', 'urn:hl7-org:v3']])::text[]) DispensedDrugCode 
FROM xml_data;

JayakumarEMIS commented 1 month ago

Thanks @staticlibs, but the issue is when you the sql result the code column value "Test" applied in both rows, but in postgre code column 2nd row is null, I attached the screen shot kindly check, that is work in sql because of cross apply node in sql, but in postgre I am not sure how to achieve it

sql: postgre:

staticlibs commented 1 month ago

@JayakumarEMIS

Perhaps something like this can help:


WITH xml_data AS (
SELECT '
<PORX_IN090101UK31 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema"
                   xmlns="urn:hl7-org:v3">
    <ControlActEvent classCode="CACT" moodCode="EVN">
        <subject typeCode="SUBJ" contextConductionInd="false">
            <DispenseClaim classCode="INFO" moodCode="EVN">
                <pertinentInformation1 typeCode="PERT" contextConductionInd="true">
                    <pertinentSupplyHeader classCode="SBADM" moodCode="EVN">
                        <legalAuthenticator typeCode="LA" contextControlCode="OP">
                            <AgentPerson classCode="AGNT">
                                <representedOrganization classCode="ORG" determinerCode="INSTANCE">
                                    <id root="1.2.826.0.1285.0.1.10" extension="Test"/>
                                </representedOrganization>
                            </AgentPerson>
                        </legalAuthenticator>
                        <pertinentInformation1 typeCode="PERT" inversionInd="false" contextConductionInd="true"
                                               negationInd="false">
                            <pertinentSuppliedLineItem classCode="SBADM" moodCode="PRMS">
                                <component typeCode="COMP">
                                    <suppliedLineItemQuantity classCode="SPLY" moodCode="EVN">
                                        <product typeCode="PRD" contextControlCode="OP">
                                            <suppliedManufacturedProduct classCode="MANU">
                                                <manufacturedSuppliedMaterial classCode="MMAT"
                                                                              determinerCode="INSTANCE">
                                                    <code code="9516211000001100"
                                                          codeSystem="2.16.840.1.113883.2.1.3.2.4.15"
                                                          displayName="Volumatic (GlaxoSmithKline UK Ltd) 1 device"/>
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                        <pertinentInformation1 typeCode="PERT" inversionInd="false" contextConductionInd="true"
                                               negationInd="false">
                            <pertinentSuppliedLineItem classCode="SBADM" moodCode="PRMS">
                                <component typeCode="COMP">
                                    <seperatableInd value="false"/>
                                    <suppliedLineItemQuantity classCode="SPLY" moodCode="EVN">
                                        <product typeCode="PRD" contextControlCode="OP">
                                            <suppliedManufacturedProduct classCode="MANU">
                                                <manufacturedSuppliedMaterial classCode="MMAT"
                                                                              determinerCode="INSTANCE">
                                                    <code code="2489911000001104"
                                                          codeSystem="2.16.840.1.113883.2.1.3.2.4.15"
                                                          displayName="Vitrex Soft lancets 0.65mm/23gauge (Vitrex Medical Ltd) 100 lancet"/>
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                    </pertinentSupplyHeader>
                </pertinentInformation1>
            </DispenseClaim>
        </subject>
    </ControlActEvent>
</PORX_IN090101UK31>
'::xml AS xml_content)
SELECT 
a.Code,
unnest(xpath('//p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@displayName', xml_content, ARRAY[ARRAY['p', 'urn:hl7-org:v3']])::text[]) DispensedDrugDescription,
unnest(xpath('//p:pertinentSuppliedLineItem[1]/p:component[1]/p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@code', xml_content, ARRAY[ARRAY['p', 'urn:hl7-org:v3']])::text[]) DispensedDrugCode 
FROM xml_data
JOIN LATERAL (
    select unnest(xpath('//../../../p:legalAuthenticator[1]/p:AgentPerson[1]/p:representedOrganization[1]/p:id[1]/@extension', xml_content, ARRAY[ARRAY['p', 'urn:hl7-org:v3']])::text[]) Code
) as a on 1=1

JayakumarEMIS commented 1 month ago

Hi @staticlibs, Do you have any idea how to run the query in babelfish t-sql directly instead of using postgre connection because unnest xpath supported in babelfish, is that possible to get the in babelfish instead of using postgre?

staticlibs commented 1 month ago

@JayakumarEMIS

The following should work:

WITH xml_data AS (
SELECT cast('
<PORX_IN090101UK31 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema"
                   xmlns="urn:hl7-org:v3">
    <ControlActEvent classCode="CACT" moodCode="EVN">
        <subject typeCode="SUBJ" contextConductionInd="false">
            <DispenseClaim classCode="INFO" moodCode="EVN">
                <pertinentInformation1 typeCode="PERT" contextConductionInd="true">
                    <pertinentSupplyHeader classCode="SBADM" moodCode="EVN">
                        <legalAuthenticator typeCode="LA" contextControlCode="OP">
                            <AgentPerson classCode="AGNT">
                                <representedOrganization classCode="ORG" determinerCode="INSTANCE">
                                    <id root="1.2.826.0.1285.0.1.10" extension="Test"/>
                                </representedOrganization>
                            </AgentPerson>
                        </legalAuthenticator>
                        <pertinentInformation1 typeCode="PERT" inversionInd="false" contextConductionInd="true"
                                               negationInd="false">
                            <pertinentSuppliedLineItem classCode="SBADM" moodCode="PRMS">
                                <component typeCode="COMP">
                                    <suppliedLineItemQuantity classCode="SPLY" moodCode="EVN">
                                        <product typeCode="PRD" contextControlCode="OP">
                                            <suppliedManufacturedProduct classCode="MANU">
                                                <manufacturedSuppliedMaterial classCode="MMAT"
                                                                              determinerCode="INSTANCE">
                                                    <code code="9516211000001100"
                                                          codeSystem="2.16.840.1.113883.2.1.3.2.4.15"
                                                          displayName="Volumatic (GlaxoSmithKline UK Ltd) 1 device"/>
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                        <pertinentInformation1 typeCode="PERT" inversionInd="false" contextConductionInd="true"
                                               negationInd="false">
                            <pertinentSuppliedLineItem classCode="SBADM" moodCode="PRMS">
                                <component typeCode="COMP">
                                    <seperatableInd value="false"/>
                                    <suppliedLineItemQuantity classCode="SPLY" moodCode="EVN">
                                        <product typeCode="PRD" contextControlCode="OP">
                                            <suppliedManufacturedProduct classCode="MANU">
                                                <manufacturedSuppliedMaterial classCode="MMAT"
                                                                              determinerCode="INSTANCE">
                                                    <code code="2489911000001104"
                                                          codeSystem="2.16.840.1.113883.2.1.3.2.4.15"
                                                          displayName="Vitrex Soft lancets 0.65mm/23gauge (Vitrex Medical Ltd) 100 lancet"/>
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                    </pertinentSupplyHeader>
                </pertinentInformation1>
            </DispenseClaim>
        </subject>
    </ControlActEvent>
</PORX_IN090101UK31>
' as xml) AS xml_content)
SELECT
a.Code,
cast(unnest(xpath('//p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@displayName', xml_content, '{{p,urn:hl7-org:v3}}')) as nvarchar(max)) DispensedDrugDescription,
cast(unnest(xpath('//p:pertinentSuppliedLineItem[1]/p:component[1]/p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@code', xml_content, '{{p,urn:hl7-org:v3}}'))  as nvarchar(max)) DispensedDrugCode 
from xml_data
cross apply (
    select cast(unnest(xpath('//../../../p:legalAuthenticator[1]/p:AgentPerson[1]/p:representedOrganization[1]/p:id[1]/@extension', xml_content, '{{p,urn:hl7-org:v3}}')) as nvarchar(max)) Code
) as a

JayakumarEMIS commented 1 month ago

Thanks a lot @staticlibs, will check my other xml nodes

JayakumarEMIS commented 1 month ago

Hi @staticlibs , I am checking my other node there is one of the path causing the issue which means different result from sql in babelfish, other nodes are working I am sharing my query and xpath kindly check if you have any idea kindly provide the solution. this is the xpath cause the issue cast(unnest(xpath('//../p:id[1]/@root', RequestXmls, '{{p,urn:hl7-org:v3}}')) as varchar(max)) As DispensingUID

CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) 
    Select cast('<PORX_IN090101UK31
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="urn:hl7-org:v3">
    <ControlActEvent>
        <subject>
            <DispenseClaim>
                <pertinentInformation1>
                    <pertinentSupplyHeader>
                        <legalAuthenticator>
                            <AgentPerson>
                                <representedOrganization>
                                    <id extension="FA391" />
                                </representedOrganization>
                            </AgentPerson>
                        </legalAuthenticator>
                        <pertinentInformation1>
                            <pertinentSuppliedLineItem >
                                <id root="B1576F6C-CED8-4984-9D35-8607A728FC36" />
                                <component>
                                    <suppliedLineItemQuantity>
                                        <product>
                                            <suppliedManufacturedProduct>
                                                <manufacturedSuppliedMaterial>
                                                    <code code="1048711000001109" displayName="Doxazosin 4mg tablets 28 tablet" />
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                                <inFulfillmentOf>
                                    <priorOriginalItemRef>
                                        <id root="1E108A17-3019-1689-E063-0100007F9DA9" />
                                    </priorOriginalItemRef>
                                </inFulfillmentOf>
                                <predecessor>
                                    <priorSuppliedLineItemRef>
                                        <id root="D02C76BE-FF00-4350-B11E-18B54C5092B8" />
                                    </priorSuppliedLineItemRef>
                                </predecessor>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                        <pertinentInformation1>
                            <pertinentSuppliedLineItem>
                                <inFulfillmentOf>
                                    <priorOriginalItemRef>
                                        <id root="1E108A17-301E-1689-E063-0100007F9DA9" />
                                    </priorOriginalItemRef>
                                </inFulfillmentOf>
                                <predecessor>
                                    <priorSuppliedLineItemRef>
                                        <id root="FC7DF4AE-4DD1-4854-B259-CFA80B211E37" />
                                    </priorSuppliedLineItemRef>
                                </predecessor>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                    </pertinentSupplyHeader>
                </pertinentInformation1>
            </DispenseClaim>
        </subject>
    </ControlActEvent>
</PORX_IN090101UK31>' as xml),1
end;

WITH XMLNAMESPACES ('urn:hl7-org:v3' AS p)
SELECT
p.value('../../../p:legalAuthenticator[1]/p:AgentPerson[1]/p:representedOrganization[1]/p:id[1]/@extension','varchar(10)') As PharmacyNacsCode,
           p.value('p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@displayName','varchar(max)') As DispensedDrugDescription,
           p.value('p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@code','varchar(max)') As DispensedDrugCode,
 p.value('../p:id[1]/@root','varchar(50)') As DispensingUID
    FROM #TempClaim Req
    CROSS APPLY Req.RequestXmls.nodes('//child::node()/p:ControlActEvent/p:subject/p:DispenseClaim/p:pertinentInformation1/p:pertinentSupplyHeader/p:pertinentInformation1/p:pertinentSuppliedLineItem/p:component') t(p)

DROP TABLE #TempClaim

Sql Result:

Babelfish Query:

CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) Select cast('<PORX_IN090101UK31
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="urn:hl7-org:v3">
    <ControlActEvent>
        <subject>
            <DispenseClaim>
                <pertinentInformation1>
                    <pertinentSupplyHeader>
                        <legalAuthenticator>
                            <AgentPerson>
                                <representedOrganization>
                                    <id extension="FA391" />
                                </representedOrganization>
                            </AgentPerson>
                        </legalAuthenticator>
                        <pertinentInformation1>
                            <pertinentSuppliedLineItem >
                                <id root="B1576F6C-CED8-4984-9D35-8607A728FC36" />
                                <component>
                                    <suppliedLineItemQuantity>
                                        <product>
                                            <suppliedManufacturedProduct>
                                                <manufacturedSuppliedMaterial>
                                                    <code code="1048711000001109" displayName="Doxazosin 4mg tablets 28 tablet" />
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                                <inFulfillmentOf>
                                    <priorOriginalItemRef>
                                        <id root="1E108A17-3019-1689-E063-0100007F9DA9" />
                                    </priorOriginalItemRef>
                                </inFulfillmentOf>
                                <predecessor>
                                    <priorSuppliedLineItemRef>
                                        <id root="D02C76BE-FF00-4350-B11E-18B54C5092B8" />
                                    </priorSuppliedLineItemRef>
                                </predecessor>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                        <pertinentInformation1>
                            <pertinentSuppliedLineItem>
                                <inFulfillmentOf>
                                    <priorOriginalItemRef>
                                        <id root="1E108A17-301E-1689-E063-0100007F9DA9" />
                                    </priorOriginalItemRef>
                                </inFulfillmentOf>
                                <predecessor>
                                    <priorSuppliedLineItemRef>
                                        <id root="FC7DF4AE-4DD1-4854-B259-CFA80B211E37" />
                                    </priorSuppliedLineItemRef>
                                </predecessor>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                    </pertinentSupplyHeader>
                </pertinentInformation1>
            </DispenseClaim>
        </subject>
    </ControlActEvent>
</PORX_IN090101UK31>' as xml),1
end;

SELECT 
a.Code,
cast(unnest(xpath('//p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@displayName', RequestXmls, '{{p,urn:hl7-org:v3}}')) as nvarchar(max)) DispensedDrugDescription,
cast(unnest(xpath('//p:pertinentSuppliedLineItem[1]/p:component[1]/p:suppliedLineItemQuantity[1]/p:product[1]/p:suppliedManufacturedProduct[1]/p:manufacturedSuppliedMaterial[1]/p:code[1]/@code', RequestXmls, '{{p,urn:hl7-org:v3}}'))  as nvarchar(max)) DispensedDrugCode,
cast(unnest(xpath('//../p:id[1]/@root', RequestXmls, '{{p,urn:hl7-org:v3}}')) as varchar(max)) As DispensingUID
from #TempClaim 
cross apply (
    select cast(unnest(xpath('//../../../p:legalAuthenticator[1]/p:AgentPerson[1]/p:representedOrganization[1]/p:id[1]/@extension', RequestXmls, '{{p,urn:hl7-org:v3}}')) as nvarchar(max)) Code
) as a

DROP TABLE #TempClaim

Babelfish Result: ![Uploading image.png…]()

staticlibs commented 1 month ago

@JayakumarEMIS

Please minimize this example to the minimal required to be reproducible (removing all unneeded business-specific details and making XML as small as possible) so it is showing the discrepancy between MSSQL and Babelfish - I can look at it then.

JayakumarEMIS commented 1 month ago

Sure @staticlibs, I will minimize the xml

JayakumarEMIS commented 1 month ago

Hi @staticlibs , I updated the minimized xml in above comment kindly check and i am using same xml in both queries and add that xml here as well kindly check

<PORX_IN090101UK31
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="urn:hl7-org:v3">
    <ControlActEvent classCode="CACT" moodCode="EVN">
        <subject typeCode="SUBJ" contextConductionInd="false">
            <DispenseClaim classCode="INFO" moodCode="EVN">
                <pertinentInformation1 typeCode="PERT" contextConductionInd="true">
                    <pertinentSupplyHeader classCode="SBADM" moodCode="EVN">
                        <legalAuthenticator typeCode="LA" contextControlCode="OP">
                            <AgentPerson classCode="AGNT">
                                <representedOrganization classCode="ORG" determinerCode="INSTANCE">
                                    <id root="1.2.826.0.1285.0.1.10" extension="FA391" />
                                    <code code="003" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.94" />
                                    <name>Kick</name>
                                    <addr>
                                        <streetAddressLine partType="SAL">street</streetAddressLine>
                                    </addr>
                                </representedOrganization>
                            </AgentPerson>
                        </legalAuthenticator>
                        <pertinentInformation1 typeCode="PERT" inversionInd="false" contextConductionInd="true" negationInd="false">
                            <seperatableInd value="true" />
                            <templateId root="2.16.840.1.113883.2.1.3.2.4.18.2" extension="CSAB_RM-NPfITUK10.sourceOf2" />
                            <pertinentSuppliedLineItem classCode="SBADM" moodCode="PRMS">
                                <id root="B1576F6C-CED8-4984-9D35-8607A728FC36" />
                                <code code="225426007" codeSystem="2.16.840.1.113883.2.1.3.2.4.15" />
                                <effectiveTime nullFlavor="NA" />
                                <component typeCode="COMP">
                                    <seperatableInd value="false" />
                                    <suppliedLineItemQuantity classCode="SPLY" moodCode="EVN">
                                        <code code="373784005" codeSystem="2.16.840.1.113883.2.1.3.2.4.15" />
                                        <quantity value="84" unit="1">
                                            <translation code="428673006" codeSystem="2.16.840.1.113883.2.1.3.2.4.15" displayName="tablet" value="84" />
                                        </quantity>
                                        <product typeCode="PRD" contextControlCode="OP">
                                            <suppliedManufacturedProduct classCode="MANU">
                                                <manufacturedSuppliedMaterial classCode="MMAT" determinerCode="INSTANCE">
                                                    <code code="1048711000001109" codeSystem="2.16.840.1.113883.2.1.3.2.4.15" displayName="Doxazosin 4mg tablets 28 tablet" />
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                        <pertinentInformation1 typeCode="PERT" contextConductionInd="true">
                                            <seperatableInd value="false" />
                                            <pertinentChargePayment classCode="OBS" moodCode="EVN">
                                                <code code="CP" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.30" />
                                                <value value="false" />
                                            </pertinentChargePayment>
                                        </pertinentInformation1>
                                        <pertinentInformation2 typeCode="PERT" contextConductionInd="true">
                                            <seperatableInd value="false" />
                                            <pertinentDispensingEndorsement classCode="OBS" moodCode="EVN">
                                                <code code="DE" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.30" />
                                                <value code="NDEC" codeSystem="2.16.840.1.113883.2.1.3.2.4.16.29" />
                                            </pertinentDispensingEndorsement>
                                        </pertinentInformation2>
                                    </suppliedLineItemQuantity>
                                </component>
                                <pertinentInformation1 typeCode="PERT" contextConductionInd="true">
                                    <seperatableInd value="false" />
                                    <pertinentRunningTotal classCode="OBS" moodCode="EVN">
                                        <code code="RT" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.30" />
                                        <value value="84" unit="1">
                                            <translation code="428673006" codeSystem="2.16.840.1.113883.2.1.3.2.4.15" displayName="tablet" value="84" />
                                        </value>
                                    </pertinentRunningTotal>
                                </pertinentInformation1>
                                <pertinentInformation3 typeCode="PERT" contextConductionInd="true">
                                    <seperatableInd value="false" />
                                    <pertinentItemStatus classCode="OBS" moodCode="EVN">
                                        <code code="IS" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.30" />
                                        <value code="0001" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.23" />
                                    </pertinentItemStatus>
                                </pertinentInformation3>
                                <inFulfillmentOf typeCode="FLFS" inversionInd="false" negationInd="false">
                                    <seperatableInd value="true" />
                                    <templateId root="2.16.840.1.113883.2.1.3.2.4.18.2" extension="CSAB_RM-NPfITUK10.sourceOf1" />
                                    <priorOriginalItemRef classCode="SBADM" moodCode="RQO">
                                        <id root="1E108A17-3019-1689-E063-0100007F9DA9" />
                                    </priorOriginalItemRef>
                                </inFulfillmentOf>
                                <predecessor typeCode="SUCC" inversionInd="false" negationInd="false">
                                    <seperatableInd value="true" />
                                    <templateId root="2.16.840.1.113883.2.1.3.2.4.18.2" extension="CSAB_RM-NPfITUK10.sourceOf1" />
                                    <priorSuppliedLineItemRef classCode="SBADM" moodCode="PRMS">
                                        <id root="D02C76BE-FF00-4350-B11E-18B54C5092B8" />
                                    </priorSuppliedLineItemRef>
                                </predecessor>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                        <pertinentInformation1 typeCode="PERT" inversionInd="false" contextConductionInd="true" negationInd="false">
                            <seperatableInd value="true" />
                            <templateId root="2.16.840.1.113883.2.1.3.2.4.18.2" extension="CSAB_RM-NPfITUK10.sourceOf2" />
                            <pertinentSuppliedLineItem classCode="SBADM" moodCode="PRMS">
                                <id root="A4C3F5E4-60DA-4AF7-8CA9-7CBF291C681C" />
                                <code code="225426007" codeSystem="2.16.840.1.113883.2.1.3.2.4.15" />
                                <effectiveTime nullFlavor="NA" />
                                <pertinentInformation2 typeCode="PERT" contextConductionInd="false">
                                    <seperatableInd value="false" />
                                    <pertinentNonDispensingReason classCode="OBS" moodCode="EVN">
                                        <code code="NDR" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.30" />
                                        <value code="0001" codeSystem="2.16.840.1.113883.2.1.3.2.4.16.31" displayName="Not required as instructed by the patient" />
                                    </pertinentNonDispensingReason>
                                </pertinentInformation2>
                                <pertinentInformation3 typeCode="PERT" contextConductionInd="true">
                                    <seperatableInd value="false" />
                                    <pertinentItemStatus classCode="OBS" moodCode="EVN">
                                        <code code="IS" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.30" />
                                        <value code="0002" codeSystem="2.16.840.1.113883.2.1.3.2.4.17.23" />
                                    </pertinentItemStatus>
                                </pertinentInformation3>
                                <inFulfillmentOf typeCode="FLFS" inversionInd="false" negationInd="false">
                                    <seperatableInd value="true" />
                                    <templateId root="2.16.840.1.113883.2.1.3.2.4.18.2" extension="CSAB_RM-NPfITUK10.sourceOf1" />
                                    <priorOriginalItemRef classCode="SBADM" moodCode="RQO">
                                        <id root="1E108A17-301E-1689-E063-0100007F9DA9" />
                                    </priorOriginalItemRef>
                                </inFulfillmentOf>
                                <predecessor typeCode="SUCC" inversionInd="false" negationInd="false">
                                    <seperatableInd value="true" />
                                    <templateId root="2.16.840.1.113883.2.1.3.2.4.18.2" extension="CSAB_RM-NPfITUK10.sourceOf1" />
                                    <priorSuppliedLineItemRef classCode="SBADM" moodCode="PRMS">
                                        <id root="FC7DF4AE-4DD1-4854-B259-CFA80B211E37" />
                                    </priorSuppliedLineItemRef>
                                </predecessor>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                    </pertinentSupplyHeader>
                </pertinentInformation1>
            </DispenseClaim>
        </subject>
    </ControlActEvent>
</PORX_IN090101UK31>

staticlibs commented 1 month ago

@JayakumarEMIS

Please prepare the minimal reproducible example for both MSSQL and Babelfish. Please remove all unnecessary business-specific details (XML should look like: <root><foo bar="42"></root>). I can look at it then.

JayakumarEMIS commented 1 month ago

Hi @staticlibs , I have removed all the unwanted data only retaining header node and needed data, kindly check this xml it can be reproducible and I will update the xml above query as well

<PORX_IN090101UK31
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="urn:hl7-org:v3">
    <ControlActEvent>
        <subject>
            <DispenseClaim>
                <pertinentInformation1>
                    <pertinentSupplyHeader>
                        <legalAuthenticator>
                            <AgentPerson>
                                <representedOrganization>
                                    <id extension="FA391" />
                                </representedOrganization>
                            </AgentPerson>
                        </legalAuthenticator>
                        <pertinentInformation1>
                            <pertinentSuppliedLineItem >
                                <id root="B1576F6C-CED8-4984-9D35-8607A728FC36" />
                                <component>
                                    <suppliedLineItemQuantity>
                                        <product>
                                            <suppliedManufacturedProduct>
                                                <manufacturedSuppliedMaterial>
                                                    <code code="1048711000001109" displayName="Doxazosin 4mg tablets 28 tablet" />
                                                </manufacturedSuppliedMaterial>
                                            </suppliedManufacturedProduct>
                                        </product>
                                    </suppliedLineItemQuantity>
                                </component>
                                <inFulfillmentOf>
                                    <priorOriginalItemRef>
                                        <id root="1E108A17-3019-1689-E063-0100007F9DA9" />
                                    </priorOriginalItemRef>
                                </inFulfillmentOf>
                                <predecessor>
                                    <priorSuppliedLineItemRef>
                                        <id root="D02C76BE-FF00-4350-B11E-18B54C5092B8" />
                                    </priorSuppliedLineItemRef>
                                </predecessor>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                        <pertinentInformation1>
                            <pertinentSuppliedLineItem>
                                <inFulfillmentOf>
                                    <priorOriginalItemRef>
                                        <id root="1E108A17-301E-1689-E063-0100007F9DA9" />
                                    </priorOriginalItemRef>
                                </inFulfillmentOf>
                                <predecessor>
                                    <priorSuppliedLineItemRef>
                                        <id root="FC7DF4AE-4DD1-4854-B259-CFA80B211E37" />
                                    </priorSuppliedLineItemRef>
                                </predecessor>
                            </pertinentSuppliedLineItem>
                        </pertinentInformation1>
                    </pertinentSupplyHeader>
                </pertinentInformation1>
            </DispenseClaim>
        </subject>
    </ControlActEvent>
</PORX_IN090101UK31>

staticlibs commented 1 month ago

@JayakumarEMIS

It is not clear whether business-specific tags like pertinentSupplyHeader or multiple layers of tags or namespaces are needed to reproduce the problem. Please prepare the minimal reproducible example for both MSSQL and Babelfish, minimal XML should look like: <root><foo bar="42"></root> and contain only the tags/attributes necessary to reproduce the problem.

JayakumarEMIS commented 1 month ago

Hi @staticlibs , I have prepared one sample xml to reproduce my problem here as you can see i have two items in the node and first item don't have id and second have item id value is 2 using unnest xpath to fetch the data but the problem the id comes into first row instead of second, please check it can be reproduce from your end

CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) Select cast('<root>
    <component typeCode="Test">
    </component>
    <item>
        <name>Item1</name>
    </item>
    <item>
        <id>2</id>
        <name>Item2</name>
    </item>
</root>' as xml),1
end;

SELECT
cast(unnest(xpath('//item/id/text()', RequestXmls, '{{p,urn:hl7-org:v3}}')) as varchar(max)) As id,
cast(unnest(xpath('//item/name/text()', RequestXmls, '{{p,urn:hl7-org:v3}}')) as varchar(max)) As name,
data
from #TempClaim
cross apply (
    select cast(unnest(xpath('//component/@typeCode', RequestXmls, '{{p,urn:hl7-org:v3}}')) as nvarchar(100)) data) as a

DROP TABLE #TempClaim

staticlibs commented 1 month ago

@JayakumarEMIS

Thanks for preparing the example! It is much easier to understand. Something like this should work:


CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) Select cast('
<root>
    <component typeCode="Test">
    </component>
    <item>
        <name>Item1</name>
    </item>
    <item>
        <id>2</id>
        <name>Item2</name>
    </item>
</root>
' as xml),1
end;

with items_cte (item) as (
    select unnest(xpath('//item', RequestXmls))
    from #TempClaim
    as item
)
select
     array_to_string(xpath('/item/id/text()', item), N''),
     array_to_string(xpath('/item/name/text()', item), N''),
     a.[data]
from items_cte
cross apply (
    select cast(unnest(xpath('//component/@typeCode', RequestXmls)) as nvarchar(max)) [data] from #TempClaim) as a

DROP TABLE #TempClaim

It also may be easier instead to put the items_cte result into the temporary table and write a T-SQL procedure with a cursor over this table to process the <item> XML records one by one.

JayakumarEMIS commented 1 month ago

Hi @staticlibs, thanks a lot, for the above scenario is working expected, but if I have custom namespace in xml can't able to get data can you please check what mistake I made and provide your solution

CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) Select cast('
<PORX_IN090101UK31 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns="urn:hl7-org:v3">
    <component typeCode="Test">
    </component>
    <item>
        <name>Item1</name>
    </item>
    <item>
        <id>2</id>
        <name>Item2</name>
    </item>
</PORX_IN090101UK31>
' as xml),1
end;

with items_cte (item) as (
    select unnest(xpath('//p:item', RequestXmls, '{{p,urn:hl7-org:v3}}'))
    from #TempClaim
    as item
)
select
      array_to_string(xpath('/p:item/p:id/text()', item), '{{p,urn:hl7-org:v3}}') as id,
      array_to_string(xpath('/p:item/p:name/text()', item), '{{p,urn:hl7-org:v3}}') as name
from items_cte

DROP TABLE #TempClaim

staticlibs commented 1 month ago

@JayakumarEMIS

This call is incorrect:

array_to_string(xpath('/p:item/p:id/text()', item), '{{p,urn:hl7-org:v3}}')

It should be:

array_to_string(xpath('/p:item/p:id/text()', item, '{{p,urn:hl7-org:v3}}'), '')

This part may be confusing because array_to_string is (mis)used here to get the first element of the array from the array output of xpath, that is expected to always have a single element. If for some reason the input data will result into 2 or more elements in xpath result - it will break the output badly. I suggest to write a separate pl/pgsql function (callable from T-SQL) to get a first element of the array properly with appropriate array length checks if necessary.

JayakumarEMIS commented 1 month ago

Hi @staticlibs, thanks for that your above sample query is working, but I am not able to get data for my below query i tried the above sample query applied into my actual xml, but not able to any data can you please check this xml what i missed it, and also if data not present in xml is returning empty string instead of that i need to get null

CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) Select cast('<pertinentSuppliedLineItem xmlns="urn:hl7-org:v3" classCode="SBADM" moodCode="PRMS">
  <id root="B1576F6C-CED8-4984-9D35-8607A728FC36" />
  <code code="225426007"/>
  <effectiveTime nullFlavor="NA" />
  <component typeCode="COMP">
    <seperatableInd value="false" />
    <suppliedLineItemQuantity classCode="test1" moodCode="EVN">
      <code code="373784005"/>
    </suppliedLineItemQuantity>
    <suppliedLineItemQuantity classCode="test2" moodCode="EVN">
        <code code="373784005"/>
    </suppliedLineItemQuantity>
  </component>
</pertinentSuppliedLineItem>' as xml),1
end;

with items_cte (item) as (
select unnest(xpath('//p:pertinentSuppliedLineItem', RequestXmls, '{{p,urn:hl7-org:v3}}'))
from #TempClaim
as item
)
select
    array_to_string(xpath('/p:id/@root', item, '{{p,urn:hl7-org:v3}}'), '') as id,
    array_to_string(xpath('/p:component/p:suppliedLineItemQuantity[1]/@classCode', item, '{{p,urn:hl7-org:v3}}'), '') as classcode
from items_cte

DROP TABLE #TempClaim

staticlibs commented 1 month ago

@JayakumarEMIS

Please prepare the minimal example that returns empty string instead of null.

JayakumarEMIS commented 1 month ago

Hi @staticlibs, I prepared sample can you please check this, in below result not getting any data and i need 2nd row of value column alone null, because second student record doesn't contain node

CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) Select cast('<school xmlns="urn:hl7-org:v3">
    <id root="1" />
    <department typeCode="computer">
        <student>
            <code id="1"/>
            <value>
                <name value = "sam"/>
            </value>
        </student>
        <student >
            <code id="2"/>
        </student>
    </department>
</school>' as xml),1
end;

with items_cte (item) as (
select unnest(xpath('//p:student', RequestXmls, '{{p,urn:hl7-org:v3}}'))
from #TempClaim
as item
)
select 
    array_to_string(xpath('/p:code/@id', item, '{{p,urn:hl7-org:v3}}'), '') as id,
    array_to_string(xpath('/p:value/p:name/@value', item, '{{p,urn:hl7-org:v3}}'), '') as value
from items_cte

DROP TABLE #TempClaim

staticlibs commented 1 month ago

@JayakumarEMIS

in below result not getting any data

Please show the XPath expression, the corresponding XML doc you are running this XPath on, the expected result and the explanation, why do you think the result returned by Babelfish is incorrect.

and i need 2nd row of value column alone null, because second student record doesn't contain node

And what prevents you from converting an empty array, returned by XPath call, to NULL?

JayakumarEMIS commented 1 month ago

Hi @staticlibs, i confused bit, for more clearance am sharing the sql query and result please execute the sql query in sql server, the same result i need to achieve through babelfish please compare the sql and babelfish result and proveide the solution

Sql Query

CREATE TABLE #TempClaim(
   [RequestXmls] [XML] NULL,
   [PrescriptionId] [INT] NOT NULL
)
BEGIN
   -- Insert XML data
   INSERT INTO #TempClaim (RequestXmls, PrescriptionId)
   SELECT
       CAST('
<school xmlns="urn:hl7-org:v3">
<id root="1" />
<department typeCode="computer">
<student>
<code id="1"/>
<value>
<name value="sam"/>
</value>
</student>
<student>
<code id="2"/>
</student>
<student>
<code id="3"/>
<value>
<name value="jane"/>
</value>
</student>
</department>
</school>' AS XML),
       1
END;
-- Query to extract student ID and name from the XML
WITH XMLNAMESPACES ('urn:hl7-org:v3' AS p)
SELECT
   student.value('(p:code/@id)[1]', 'INT') AS StudentID,
   student.value('(p:value/p:name/@value)[1]', 'VARCHAR(50)') AS StudentName
FROM
   #TempClaim
   CROSS APPLY RequestXmls.nodes('//p:department/p:student') AS Students(student);
-- Drop temporary table
DROP TABLE #TempClaim;

Sql Result:

Babelfish Query:

CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) Select cast('<school xmlns="urn:hl7-org:v3">
<id root="1" />
<department typeCode="computer">
<student>
<code id="1"/>
<value>
<name value="sam"/>
</value>
</student>
<student>
<code id="2"/>
</student>
<student>
<code id="3"/>
<value>
<name value="jane"/>
</value>
</student>
</department>
</school>' as xml),1
end;

with items_cte (item) as (
select unnest(xpath('//p:student', RequestXmls, '{{p,urn:hl7-org:v3}}'))
from #TempClaim
as item
)
select 
    array_to_string(xpath('/p:code/@id', item, '{{p,urn:hl7-org:v3}}'), '') as id,
    array_to_string(xpath('/p:value/p:name/@value', item, '{{p,urn:hl7-org:v3}}'), '') as value
from items_cte

DROP TABLE #TempClaim

Babefish Result:

staticlibs commented 1 month ago

@JayakumarEMIS

the same result i need to achieve through babelfish

If you have any Babelfish-specific problems, while achieving that, please extract the minimal example reproducing such a problem and post it here.

PS: instead of array_to_string to access the array output of xpath function I suggest to use a wrapper like this:

create function sys.xpath_first(expr sys.nvarchar, doc xml, ns sys.nvarchar)
returns xml as $$
    with cte(arr) as (select xpath(expr::text, doc, ns::text[][]))
    select
        case when array_length(arr, 1) > 0
        then arr[1]
        else null
        end
    from cte
$$ language sql immutable

Can be used in T-SQL like this (returns 41 as xml):

select sys.xpath_first(N'//foo/@id', cast('
<root>
    <foo id="41"/>
    <foo id="42"/>
    <foo id="43"/>
</root>
' as xml), N'{}')

JayakumarEMIS commented 1 month ago

Sorry @staticlibs, hereafter prepare minimal query to understand the problem, I am trying to apply above solution instead of using array to string method but getting some error can you please help me to fix?

CREATE TABLE #TempClaim(
    [RequestXmls] [XML] NULL,
    [PrescriptionId] [INT] NOT NULL
)

begin 
    insert  into #TempClaim ( RequestXmls, PrescriptionId ) Select cast('
<root>
    <item>
        <name value= "Item1"></name>
    </item>
    <item>
        <id>2</id>
        <name value= "Item2"></name>
    </item>
</root>
' as xml),1
end;

with items_cte (item) as (
    select unnest(xpath('//item', RequestXmls))
    from #TempClaim
    as item
)
select 
     sys.xpath_first(N'/item/id/text()', item, N'{}') as Id,
     sys.xpath_first(N'/item/name/@value', item, N'{}') as name
from items_cte

DROP TABLE #TempClaim

staticlibs commented 1 month ago

@JayakumarEMIS

zero-length delimited identifier at or near "[]"

Indeed, plain SQL function cannot be used in this context, please try to use plpgsql one like this:

create function sys.xpath_first(expr sys.nvarchar, doc xml, ns sys.varchar)
returns xml as $$
declare
    res xml;
begin
    with cte(arr) as (select xpath(expr::text, doc, ns::text[][]))
    select
        case when array_length(arr, 1) > 0
        then arr[1]
        else null
        end
    into res
    from cte;
    return res;
end
$$ language plpgsql immutable

JayakumarEMIS commented 1 month ago

@staticlibs thanks a lot, seems to be working without issues