February 2, 2016 at 10:08 am
Consider this (very simplified) scenario :
DECLARE @Records TABLE (
Idint,
Codevarchar(10),
XmlContentXML
);
INSERT INTO @Records
SELECT 1,
'Record_1',
CAST('<test id="1" code="TEST_1"/><test id="2" code="TEST_2"/>' AS XML)
;
SELECT Tag = 1,
Parent = NULL,
'record!1!id' = Id,
'record!1!code' = Code,
'record!1!!Element' = XmlContent.query('//test')
FROM @Records
FOR XML Explicit;
...where I have a table record with XML (fragments) in one of its fields.
And I have to generate XML from that record, with the XML fragment as a child element of the record XML.
(And because the real-life scenario is many times more complex, I can only use FOR XML EXPLICIT.)
What it generates is this :
<record id="1" code="Record_1">
<test id="1" code="TEST_1" xmlns="" />
<test id="2" code="TEST_2" xmlns="" />
</record>
But I need XML without namespaces, like this :
<record id="1" code="Record_1">
<test id="1" code="TEST_1" />
<test id="2" code="TEST_2" />
</record>
How can I achieve that?
Any help would be appreciated!
February 2, 2016 at 10:42 am
I found a workaround myself, which seems pretty dirty, but it does the trick :
SELECT Tag = 1,
Parent = NULL,
'record!1!id' = Id,
'record!1!code' = Code,
'record!1!!XML' = CAST(XmlContent.query('//test') AS varchar(max))
FROM @Records
FOR XML EXPLICIT;
By first casting the XML data field to a (n)varchar datatype, and then applying the "XML" directive instead of the "ELEMENT" directive in the FOR XML EXPLICIT construction.
(See https://msdn.microsoft.com/en-us/library/bb510481.aspx)
However, I hope there are better solutions, where no casting or converting is needed?
To me, it seems strange, to have XML converted to text to be able to get proper XML in the end.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply