FOR XML RAW

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Hello all..
    I was trying to create an XML which has following design..

    <?xml version="1.0"?>
    <MAIN-file>
    <file name=id type= type size= size/>
    <description> [description]</description>
    <source type=stype id= [sourceid]/>
    <destination type=[accesstype1] id=“[accessid]”/>
    <destination type=[accesstype2] id=" [accessid]2” />
    <destination type="[accesstype3]" id="[accessid]3”/>
    <category name="NEW" </category>
    </MAIN-file>

    but it has multiple lines with different tags, (elements of XML are in  same row)
    I was able to get 1st line done with one FOR XML RAW code but not able to get other lines merged..
    I have created a sample data here:

    IF EXISTS(SELECT *    FROM ##temp)
    DROP TABLE ##temp

    CREATE TABLE ##temp
    (
        [id] varchar(10) NULL,
        [type] [varchar](3)  NULL,
         [varchar](1)  NULL,
        [description] [varchar](100) NULL,
        [stype] [varchar](6)  NULL,
        [sourceid] [varchar](9)  NULL,
        [accesstype] [varchar](13)  NULL,
        [accessid] varchar(14) NULL,
        [Category name] [varchar](3)  NULL
    )

    insert into ##temp

    select '4324'    , 'xls' ,    '0',     'SOB'    ,'source',    'my source'    , 'access_id_tag' ,     '1268' ,    'NEW' union
    select '4325'    , 'xls' ,    '0',     'SOB' ,    'source',    'my source'    , 'access_id_tag' ,     '1268' ,    'NEW' union
    select '4698'    , 'xls' ,    '0',     'SOC' ,    'source',    'my source'    , 'access_id_tag' ,     '1265' ,    'NEW' union
    select '4219'    , 'xls' ,    '0',     'COC' ,    'source',    'my source'    , 'access_id_tag' ,     '1265' ,    'NEW' union
    select '4327'    , 'xls' ,    '0',     'SOB' ,    'source',    'my source'    , 'access_id_tag' ,     '1268' ,    'NEW' union
    select '4326'    , 'xls' ,    '0',     'SOB' ,     'source',    'my source'    , 'access_id_tag' ,     '1268' ,    'NEW' union
    select '4092'    , 'xls' ,    '0',     'RMD',    'source'    ,'my source'    , 'access_id_tag' ,     '1265' ,    'NEW' union
    select '4037'    , 'xls' ,    '0',     'SOB'    , 'source',    'my source'    , 'access_id_tag' ,     '1265' ,    'NEW'

    select * from ##temp

    SELECT id, type , size
    FROM ##temp
    FOR XML RAW ('file'), ROOT ('main-file') ;

    -- <?xml version="1.0"?>
    --<MAIN-file>
    -- <file name=id type= type size= size/>
    -- <description> [description]</description>
    -- <source type=stype id= [sourceid]/>
    -- <destination type=[accesstype1] id=“[accessid]”/>
    -- <destination type=[accesstype2] id=" [accessid]2” />
    -- <destination type="[accesstype3]" id="[accessid]3”/>
    -- <category name="NEW" </category>
    --</MAIN-file>

    Any help with this..

    Thanks a lot in advance..

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • sgmunson

    SSC Guru

    Points: 110456

    I'm pretty sure your XML format is not actually valid XML.  I don't think you can repeat the same tag at the same level.

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    thanks I thought so too!!
    but I am not sure why the format was to be expected like this.

    basically we cannot produce this format from XML RAW statement Right?..

    <new-file>

      <filename="Report 1" type="xlsx" size="666"/>

      <filetype="my_id" id=“my office1"/>

      <categoryname="My data">  </category>

    </new-file>

    Let me see what to do now

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • sgmunson

    SSC Guru

    Points: 110456

    sql_learner29 - Friday, March 9, 2018 11:21 AM

    thanks I thought so too!!
    but I am not sure why the format was to be expected like this.

    basically we cannot produce this format from XML RAW statement Right?..

    <new-file>

      <filename="Report 1" type="xlsx" size="666"/>

      <filetype="my_id" id=“my office1"/>

      <categoryname="My data">  </category>

    </new-file>

    Let me see what to do now

    I'm not sure you can't create that format, which is entirely different from what you posted earlier with multiple destination tags...

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    yes I just removed some hard coded data to simplify it..

    Can we create this format where I can show like this:
    different tag names in same root tag..(with different property?)
    like file id, file name  and category , subcategory..
    Can you provide any sample or how to achieve this..or any link showing like this..?

    <new-file>
    <filename="Report 1" type="xlsx" size="666"/>
    <filetype="my_id" id=“my office1"/>
    <categoryname="My data"> </category>
    </new-file>

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • sgmunson

    SSC Guru

    Points: 110456

    sql_learner29 - Friday, March 9, 2018 1:33 PM

    yes I just removed some hard coded data to simplify it..

    Can we create this format where I can show like this:
    different tag names in same root tag..(with different property?)
    like file id, file name  and category , subcategory..
    Can you provide any sample or how to achieve this..or any link showing like this..?

    <new-file>
    <filename="Report 1" type="xlsx" size="666"/>
    <filetype="my_id" id=“my office1"/>
    <categoryname="My data"> </category>
    </new-file>

    I may be wrong, but I'm not sure that creating multiple different tags that have sub-values within the same tag and at the same level is "proper xml".   I know I don't have the knowledge to do it.  Someone else may know.   Best resource here that I'm aware of for XML is Eirikur Ericsson..

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • drew.allen

    SSC Guru

    Points: 76721

    Your big mistake was using the RAW option.  That gives you very little control over the output.  You're much better off using the PATH option for most purposes or the EXPLICIT option for very fine control.  Something like the following.  You didn't have multiple accesstypes in your sample data, so I treated the one you did have as two separate ones.

    Of course you can have multiple copies of the same elements at the same level. You just have to be careful to somehow separate them.

    SELECT id AS [file/@id],
        [type] AS [file/@type],
        size AS [file/@size],
        [description],
        stype AS [source/@type],
        sourceid AS [source/@sourceid],
        accesstype as [destination/@type],
        'accessid' AS [destination/@id],
        '' AS [*], /* Separator between destination elements. */
        accesstype as [destination/@type],
        'accessid2' AS [destination/@id]
    FROM ##temp
    FOR XML PATH('MAIN-file')

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen

    SSC Guru

    Points: 76721

    sgmunson - Friday, March 9, 2018 11:37 AM

    sql_learner29 - Friday, March 9, 2018 11:21 AM

    thanks I thought so too!!
    but I am not sure why the format was to be expected like this.

    basically we cannot produce this format from XML RAW statement Right?..

    <new-file>

      <filename="Report 1" type="xlsx" size="666"/>

      <filetype="my_id" id=“my office1"/>

      <categoryname="My data">  </category>

    </new-file>

    Let me see what to do now

    I'm not sure you can't create that format, which is entirely different from what you posted earlier with multiple destination tags...

    This is not a valid format.  An XML tag must have an element and can optionally have an attribute.  Elements have the following formats:
    <element />
    <element>value</element>

    Attribute must occur in the opening tag of an element and have the following format:
    attribute="value"

    So the following format only contains attributes and is missing an element.
    <filename="Report 1" type="xlsx" size="666"/>

    It can be fixed in one of the following ways.
    <filename type="xlsx" size="666">Report 1</filename>
    <newelement filename="Report 1" type="xlsx" size="666"/>

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • sgmunson

    SSC Guru

    Points: 110456

    drew.allen - Tuesday, March 13, 2018 12:08 PM

    Your big mistake was using the RAW option.  That gives you very little control over the output.  You're much better off using the PATH option for most purposes or the EXPLICIT option for very fine control.  Something like the following.  You didn't have multiple accesstypes in your sample data, so I treated the one you did have as two separate ones.

    Of course you can have multiple copies of the same elements at the same level. You just have to be careful to somehow separate them.

    SELECT id AS [file/@id],
        [type] AS [file/@type],
        size AS [file/@size],
        [description],
        stype AS [source/@type],
        sourceid AS [source/@sourceid],
        accesstype as [destination/@type],
        'accessid' AS [destination/@id],
        '' AS [*], /* Separator between destination elements. */
        accesstype as [destination/@type],
        'accessid2' AS [destination/@id]
    FROM ##temp
    FOR XML PATH('MAIN-file')

    Drew

    Very cool !!!  Just learned a bunch about how to handle XML that's in my category of "horse of a different color".   Thanks!

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

Viewing 9 posts - 1 through 9 (of 9 total)

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