Post reply

create xml from table rows

  • February 21, 2017 at 6:45 am

    #335352

    Using SQL 2016
    I want to build the below xml structure from a table of rows:
    xml:The xml attributes values for attributes : "Reqtype" and "naming" and "wording" are not from a table but are fixed data for all xmls, supplied when building the xml.

    <?xml version="1.0" ?>
    <envelope>
    <Head>
    <Security naming="Pink" wording="xxxx" />
    </Head>
    <HBody>
    <Req reqtype="upcon" reqf="22">
    <Para attr="mod" attrval="U" />
    <Para attr="cotype" attrval="TIF" />
    <Para attr="Det" attrval="Sending" />
    <Para attr="pty" attrval="1" />
    </Req>
    </HBody>
    </envelope>

    table:
     reqf| attr| attrval|
     22| mod| U| 

    Note: every new reqf value will have it's own new xml

  • February 21, 2017 at 7:51 am

    #1929533

    Can you supply some DDL and sample data? We can't access your data, so we need something to work with.

    Thanks.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • February 21, 2017 at 9:22 am

    #1929549

    Might also read in here: http://www.sqlservercentral.com/stairway/92778/

  • February 22, 2017 at 1:51 am

    #1929652

    This should get you started
    😎

    ;WITH SAMPLE_DATA AS
    (
      SELECT
       22 AS reqf
       ,'mod' AS attr
       ,'U' AS attrval
    )
    SELECT
      'Pink' AS 'Head/Security/@naming'
     ,'xxxx' AS 'Head/Security/@wording'
     ,'upcon' AS 'HBody/Req/@reqtype'
     ,SD.reqf AS 'HBody/Req/@reqf'
     ,SD.attr AS 'HBody/Para/@attr'
     ,SD.attrval AS 'HBody/Para/@attrval'
    FROM  SAMPLE_DATA  SD
    FOR XML PATH(''), ROOT('envlope'),TYPE

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply