May 17, 2014 at 8:20 am
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>
May 17, 2014 at 1:25 pm
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);
May 17, 2014 at 1:33 pm
Wowser!
Thanks very much I'll give it a go.
Thanks
May 17, 2014 at 6:02 pm
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);
May 17, 2014 at 8:36 pm
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>';
May 18, 2014 at 2:12 pm
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
May 20, 2014 at 7:23 am
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
May 20, 2014 at 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)
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
May 21, 2014 at 6:04 am
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>
May 21, 2014 at 12:55 pm
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);
May 21, 2014 at 2:41 pm
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.
😀
May 21, 2014 at 3:14 pm
You are very welcome and thanks for the kind feedback.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
January 27, 2017 at 2:35 am
mister.magoo - Tuesday, May 20, 2014 1:06 PMyour 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