Inserting additional levels within XML output

  • Hi folks

    I'm not an expert on XML outputs and I need to produce an output in an unusual format for a data feed to a document management system.

    The code below creates and inserts records into a temp table then runs the code to output the XML

    --CODE TO CREATE AND OUTPUT XML DATA

    USE [xmlData]

    GO

    --Create temp table

    IF OBJECT_ID('tempdb..#XmlTestTable') IS NOT NULL DROP TABLE #XmlTestTable

    CREATE TABLE #XmlTestTable (

    [unique_identifier] [smallint] NULL,

    [supplier_id] [nvarchar](50) NULL,

    [event_type] [nvarchar](50) NULL,

    [filename] [nvarchar](50) NULL,

    [date_created] [nvarchar](10) NULL,

    [time_created] [nvarchar](10) NULL

    ) ON [PRIMARY]

    GO

    --Add 2 sample records

    INSERT INTO #XmlTestTable (unique_identifier,supplier_id,event_type,filename,date_created,time_created) VALUES

    ('1234','6789','General','Testdoc.pdf','22/11/2013','14:19:06'),

    ('14345','68679','Assets','Assets.pdf','12/11/2013','10:29:16')

    -- Code for XML Output for first record

    SELECT TOP 1

    [UNIQUE_IDENTIFIER] AS uniqueidentifier

    ,[SUPPLIER_ID] AS supplier

    ,'EVENT_TYPE' AS id

    ,[event_type] AS value

    ,'FILENAME' AS id

    ,[filename] AS value

    ,'DATE_CREATED' AS id

    ,[date_created] AS value

    ,'TIME_CREATED' AS id

    ,[time_created] AS value

    FROM #XmlTestTable

    FOR XML PATH ('fileparameters'), ELEMENTS

    DROP TABLE #XmlTestTable

    --RESULTS IN XML FORMAT

    <fileparameters>

    <UNIQUEIDENTIFIER>1234</UNIQUEIDENTIFIER>

    <SUPPLIER>6789</SUPPLIER>

    <id>EVENT_TYPE</id>

    <value>General</value>

    <id>FILENAME</id>

    <value>Testdoc.pdf</value>

    <id>DATE_CREATED</id>

    <value>22/11/2013</value>

    <id>TIME_CREATED</id>

    <value>14:19:06</value>

    </fileparameters>

    My problem is that the specification to work it requires some more nesting and comments which I've added in bold type below.

    I've tried a mix of FOR XML with AUTO, PATH, RAW, ELEMENTS but cannot get the xml version in at the top of the file or the references to additional indexes or additional index levels. Do i need to add some more columns to my source table?

    Can any one advise please?

    <?xml version="1.0" encoding="utf-8" ?>

    <fileparameters>

    <revision number="1" />

    <primarylink>

    <UNIQUEIDENTIFER type="I">1234</UNIQUEIDENTIFIER>

    <SUPPLIER>6789</SUPPLIER>

    <primarylink>

    <additionalindexes count="4">

    <index_1>

    <id>EVENT_TYPE</id>

    <value>General</value>

    </index_1>

    <index_2>

    <id>FILENAME</id>

    <value>Testdoc.pdf</value>

    </index_2>

    <index_3>

    <id>DATE_CREATED</id>

    <value>22/11/2013</value>

    </index_3>

    <index_4>

    <id>TIME_CREATED</id>

    <value>14:19:06</value>

    </index_4>

    </additionalindexes>

    </fileparameters>

  • use subselects and the "type" option to add levels...

    SELECT TOP 1

    (select 1 as [@number] for xml path('revision'),type)

    , (select

    [UNIQUE_IDENTIFIER] AS uniqueidentifier

    ,[SUPPLIER_ID] AS supplier

    for xml path('primarylink'),type)

    , (select 4 as [@count],

    (select

    'EVENT_TYPE' AS id

    ,[event_type] AS value

    for xml path('index_1'), type)

    , (select

    'FILENAME' AS id

    ,[filename] AS value

    for xml path('index_2'), type)

    , (select

    'DATE_CREATED' AS id

    ,[date_created] AS value

    for xml path('index_3'), type)

    , (select

    'TIME_CREATED' AS id

    ,[time_created] AS value

    for xml path('index_4'), type)

    for xml path('additionalindexes'),type)

    FROM #XmlTestTable

    FOR XML PATH ('fileparameters'), ELEMENTS

    <fileparameters>

    <revision number="1" />

    <primarylink>

    <uniqueidentifier>1234</uniqueidentifier>

    <supplier>6789</supplier>

    </primarylink>

    <additionalindexes count="4">

    <index_1>

    <id>EVENT_TYPE</id>

    <value>General</value>

    </index_1>

    <index_2>

    <id>FILENAME</id>

    <value>Testdoc.pdf</value>

    </index_2>

    <index_3>

    <id>DATE_CREATED</id>

    <value>22/11/2013</value>

    </index_3>

    <index_4>

    <id>TIME_CREATED</id>

    <value>14:19:06</value>

    </index_4>

    </additionalindexes>

    </fileparameters>

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Wowser!

    Thanks very much I'll give it a go.

    Thanks

  • I forgot to mention that you cannot have an xml version declaration in an XML datatype in SQL Server, so once you have the xml how you want it, you need to cast it as a (n)varchar and append it to the xml version.

    declare @xmlstring nvarchar(max);

    set @xmlstring = '<?xml version="1.0" encoding="utf-8" ?>' + cast(@xml as nvarchar(max));

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo (5/17/2014)


    I forgot to mention that you cannot have an xml version declaration in an XML datatype in SQL Server, so once you have the xml how you want it, you need to cast it as a (n)varchar and append it to the xml version.

    declare @xmlstring nvarchar(max);

    set @xmlstring = '<?xml version="1.0" encoding="utf-8" ?>' + cast(@xml as nvarchar(max));

    In simple terms, it depends on the character data type used and the presence of a byte order mark (BOM).

    If converting NVARCHAR to XML, SQL Server uses two byte unicode, UTF-16 / UCS-2, and accepts declarations with these.

    For VARCHAR to XML the server accepts any(most?) single byte encoding such as UTF-8, WINDOWS-1252 and ISO-8859-1.

    With VARBINARY, it depends on the precence of the BOM and the declaration, types can be UTF-16 or UTF-8.

    😎

    Snips, valid XML declarations.

    DECLARE @XML_NVC1 XML = N'<?xml version="1.0" encoding="UTF-16" ?>

    <rootnode>

    <childnode>

    <childvalue>1234</childvalue>

    </childnode>

    </rootnode>';

    DECLARE @XML_NVC2 XML = N'<?xml version="1.0" encoding="UCS-2" ?>

    <rootnode>

    <childnode>

    <childvalue>1234</childvalue>

    </childnode>

    </rootnode>';

    DECLARE @XML_VC1 XML = '<?xml version="1.0" encoding="windows-1252" ?>

    <rootnode>

    <childnode>

    <childvalue>1234</childvalue>

    </childnode>

    </rootnode>';

    DECLARE @XML_VC2 XML = '<?xml version="1.0" encoding="UTF-8" ?>

    <rootnode>

    <childnode>

    <childvalue>1234</childvalue>

    </childnode>

    </rootnode>';

    DECLARE @XML_VC3 XML = '<?xml version="1.0" encoding="iso-8859-1" ?>

    <rootnode>

    <childnode>

    <childvalue>1234</childvalue>

    </childnode>

    </rootnode>';

  • Hi guys, thanks for helping me to get to this position.

    I understand the stuff about the need to cast as nvarchar in order to concatenate but I don't know how to append this XML declaration to the body of my XML output. I've been Googling and ended up down a dark scary path which talks about using xp_cmdshell. Is there a simpler way to append?

    Many Thanks

  • Hi again

    The XML version in the header isn't important for me at this time and so I'll leave it until later.

    One thing I can't figure out is how to have another value within a field label e.g.

    I need to reproduce the line <uniquereference type="I"> 1000000002 </uniquereference>

    where "type" is not a column in my source data but it's value is a constant which equals "I" for every record.

    SAMPLE OUTPUT FROM SPEC

    <?xml version="1.0" encoding="utf-8" ?>

    <fileparameters>

    <revision number="1" />

    <primarylink>

    <uniquereference type="I"> 1000000002 </uniquereference>

    <typeid>EVENT</typeid>

    </primarylink>

    <fileparameters>

    etc

    etc

  • your sample output doesn't match the previous examples, so I can't give an exact query, but this is the general idea...

    (select 'I' as [@type],'1000000002' as [data()] for xml path('uniquereference'),type)

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Thanks mister.maggoo for advising on select 'I' as [@type],'1000000002' as [data()] for xml path('uniquereference'),type)

    Apologies for changing to another set of fields however I'm now working on my live project. I have used your code from the earlier example in my live project and I have substituted the actual field value named UNIQUE_REFERENCE from my table and I get the values I need (see output below).

    I'm just a whisker away from getting this right but unfortunately since adding the new section I have had to comment out the for xml path('primarylink'),type) in line 7 of the code as it messes up the rest of the code which I'm assuming is a nesting, brackets or comma type omission. You will see from the resulting XML below that I've now lost the primarylink nesting because I've commented it out and presumably need to get the bracketing right.

    The primarylink section needs to start on the line following <revision number="1" /> and finish on the line before

    <additionalindexes count="13">

    Can you spot where the problem is and advise please? If you need me to recreate a table based on this data I can do.

    LATEST CODE

    SELECT top 1

    (select 1 AS [@number] for xml path('revision'),type),

    (select 'I' AS [@type]

    ,[UNIQUE_REFERENCE] AS [data()]

    for xml path('uniquereference'),type)

    ,[EVENT] AS typeid

    -- for xml path('primarylink'),type)

    ,(select 13 as [@count],

    (select

    'PATIENT_ID' AS id

    ,PATIENT_ID] AS value

    for xml path('index_1'), type)

    , (select

    'EVENT_ID' AS id

    ,EVENT_ID] AS value

    for xml path('index_2'), type)

    , (select

    'DOCUMENT_FILE_NAME' AS id

    ,[DOCUMENT_FILE_NAME] AS value

    for xml path('index_3'), type)

    , (select

    'DOCUMENT_DESCRIPTION' AS id

    ,[DOCUMENT_DESCRIPTION] AS value

    for xml path('index_4'), type)

    , (select

    'DOCUMENT_NAME' AS id

    ,[DOCUMENT_NAME] AS value

    for xml path('index_5'), type)

    , (select

    'FOLDER_NAME' AS id

    ,[FOLDER_NAME] AS value

    for xml path('index_6'), type)

    , (select

    'RECORD_STATUS' AS id

    ,[RECORD_STATUS] AS value

    for xml path('index_7'), type)

    , (select

    'TEAM' AS id

    ,[TEAM] AS value

    for xml path('index_8'), type)

    , (select

    'DOCUMENT_FORMAT' AS id

    ,[DOCUMENT_FORMAT] AS value

    for xml path('index_9'), type)

    , (select

    'FURTHER_DETAILS' AS id

    ,[FURTHER_DETAILS] AS value

    for xml path('index_10'), type)

    , (select

    'USER_RECORDING' AS id

    ,[USER_RECORDING] AS value

    for xml path('index_11'), type)

    , (select

    'DATE_EFFECTIVE' AS id

    ,[DATE_EFFECTIVE] AS value

    for xml path('index_12'), type)

    , (select

    'TIME_EFFECTIVE' AS id

    ,[TIME_EFFECTIVE] AS value

    for xml path('index_13'), type)

    for xml path('additionalindexes'),type)

    FROM [XMLData].[dbo].[vw_Docs_xml]

    FOR XML PATH ('fileparameters'), ELEMENTS

    XML RESULTS

    <fileparameters>

    <revision number="1" />

    <uniquereference type="I">0002891103</uniquereference>

    <typeid>General Service Event</typeid>

    <additionalindexes count="13">

    <index_1>

    <id>PATIENT_ID</id>

    <value>1212012</value>

    </index_1>

    <index_2>

    <id>EVENT_ID</id>

    <value>12416619</value>

    </index_2>

    <index_3>

    <id>DOCUMENT_FILE_NAME</id>

    <value>TestDoc 31.12.11.doc</value>

    </index_3>

    <index_4>

    <id>DOCUMENT_DESCRIPTION</id>

    <value>General document</value>

    </index_4>

    <index_5>

    <id>DOCUMENT_NAME</id>

    <value>TestDoc 31.12.11</value>

    </index_5>

    <index_6>

    <id>FOLDER_NAME</id>

    <value>General Correspondence</value>

    </index_6>

    <index_7>

    <id>RECORD_STATUS</id>

    <value>Finalised</value>

    </index_7>

    <index_8>

    <id>TEAM</id>

    <value>Functional Service</value>

    </index_8>

    <index_9>

    <id>DOCUMENT_FORMAT</id>

    <value>Word</value>

    </index_9>

    <index_10>

    <id>FURTHER_DETAILS</id>

    <value>No details</value>

    </index_10>

    <index_11>

    <id>USER_RECORDING</id>

    <value>00002334</value>

    </index_11>

    <index_12>

    <id>DATE_EFFECTIVE</id>

    <value>01/01/2012</value>

    </index_12>

    <index_13>

    <id>TIME_EFFECTIVE</id>

    <value>06:54:39</value>

    </index_13>

    </additionalindexes>

    </fileparameters>

  • If you can provide a sample dataset, it would help...

    However, it looks like you are after this:

    SELECT top 1

    (select 1 AS [@number] for xml path('revision'),type),

    [highlight="#ffff11"](select [/highlight]

    (select

    'I' AS [@type]

    ,[UNIQUE_REFERENCE] AS [data()]

    for xml path('uniquereference'),type)

    ,[EVENT] AS typeid

    [highlight="#ffff11"] for xml path('primarylink'),type)[/highlight]

    ,(select 13 as [@count],

    (select

    'PATIENT_ID' AS id

    ,PATIENT_ID] AS value

    for xml path('index_1'), type)

    , (select

    'EVENT_ID' AS id

    ,EVENT_ID] AS value

    for xml path('index_2'), type)

    , (select

    'DOCUMENT_FILE_NAME' AS id

    ,[DOCUMENT_FILE_NAME] AS value

    for xml path('index_3'), type)

    , (select

    'DOCUMENT_DESCRIPTION' AS id

    ,[DOCUMENT_DESCRIPTION] AS value

    for xml path('index_4'), type)

    , (select

    'DOCUMENT_NAME' AS id

    ,[DOCUMENT_NAME] AS value

    for xml path('index_5'), type)

    , (select

    'FOLDER_NAME' AS id

    ,[FOLDER_NAME] AS value

    for xml path('index_6'), type)

    , (select

    'RECORD_STATUS' AS id

    ,[RECORD_STATUS] AS value

    for xml path('index_7'), type)

    , (select

    'TEAM' AS id

    ,[TEAM] AS value

    for xml path('index_8'), type)

    , (select

    'DOCUMENT_FORMAT' AS id

    ,[DOCUMENT_FORMAT] AS value

    for xml path('index_9'), type)

    , (select

    'FURTHER_DETAILS' AS id

    ,[FURTHER_DETAILS] AS value

    for xml path('index_10'), type)

    , (select

    'USER_RECORDING' AS id

    ,[USER_RECORDING] AS value

    for xml path('index_11'), type)

    , (select

    'DATE_EFFECTIVE' AS id

    ,[DATE_EFFECTIVE] AS value

    for xml path('index_12'), type)

    , (select

    'TIME_EFFECTIVE' AS id

    ,[TIME_EFFECTIVE] AS value

    for xml path('index_13'), type)

    for xml path('additionalindexes'),type)

    FROM [XMLData].[dbo].[vw_Docs_xml]

    FOR XML PATH ('fileparameters'), ELEMENTS

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Remarkable! It's now working,

    There were just too many selects and brackets to grasp.

    I am truly grateful for your help with these queries.

    😀

  • You are very welcome and thanks for the kind feedback.

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • mister.magoo - Tuesday, May 20, 2014 1:06 PM

    your sample output doesn't match the previous examples, so I can't give an exact query, but this is the general idea...(select 'I' as [@type],'1000000002' as [data()] for xml path('uniquereference'),type)

    What does the SELECT geometry :: etc in your signature do?

  • Viewing 13 posts - 1 through 12 (of 12 total)

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