• caf 13920 - Friday, February 23, 2018 7:36 AM

    Hello. I'm trying to generate an XML file from SQL according to a some guidelines, which works fine but one field I cannot generate it as it is requested or at least I don't know how:

    Requested:
    <InvoicedQuantity unitCode="BUC">37.00</InvoicedQuantity>

    What I managed to generate so far, looks like this:
    <InvoicedQuantity>37.00</InvoicedQuantity>

    SQL Code:

    SELECT
        LineNumberID AS ID,
        CONVERT(decimal(18, 2), InvoicedQuantity) AS InvoicedQuantity,
    FROM (SELECT LineGID FROM MyTable WHERE GUID=@NR2
    FOR XML PATH('TaxScheme'), ROOT('TaxCategory'), TYPE)

    What am I trying to do is to add that unitCode after the opening of InoicedQuantity tag. Is this possible somehow? I tried to add as an attribute but it does not meet the requirements.

    Thanks

    unitCode in your request is an XML attribute of the InvoicedQuantity element.  XML attributes get expressed with a leading @ symbol in SQLXML.

    Here's an example of how to add the literal value to your query.

    SELECT
        LineNumberID AS ID,'BUC' as "InvoicedQuantity/@unitCode",
        CONVERT(decimal(18, 2), InvoicedQuantity) AS InvoicedQuantity,
    FROM (SELECT LineGID FROM MyTable WHERE GUID=@NR2
    FOR XML PATH('TaxScheme'), ROOT('TaxCategory'), TYPE)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?