how to prepend xml namespace in element during query?

Question

With the following

DECLARE @t TABLE (Test nvarchar(50), Location_ID uniqueidentifier);
INSERT INTO @t (Test,Location_ID ) 
SELECT Test,Location_ID 
from
dbo.TEST;
DECLARE @xml XML
;WITH XMLNAMESPACES (
'typens:GPCodedValueDomain2' as type,
'http://www.esri.com/schemas/ArcGIS/10.0' as typens, 
'http://www.w3.org/TR/html4/' AS xs, 
'http://www.w3.org/2001/XMLSchema-instance' AS xsi )
SELECT @xml = 
    (
    SELECT 
    'typens:CodedValue' AS "@xsi:type", 
    TEST AS "Name", Location_ID  AS "Code"
    FROM @t 
    order by Location_ID
    FOR XML PATH
    ('CodedValue'), ROOT
    ('GPCodedValueDomain2'),TYPE
    )
SELECT @xml

it outputs

<GPCodedValueDomain2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/TR/html4/" xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.0" xmlns:type="typens:GPCodedValueDomain2">
  <CodedValue xsi:type="typens:CodedValue">
    <Name>test update</Name>
    <Code>B59D3BEB-CBCE-E111-B5B0-002564D275D1</Code>
  </CodedValue>
  <CodedValue xsi:type="typens:CodedValue">
    <Name>test update</Name>
    <Code>B69D3BEB-CBCE-E111-B5B0-002564D275D1</Code>
  </CodedValue>
  <CodedValue xsi:type="typens:CodedValue">
    <Name>test update</Name>
    <Code>B79D3BEB-CBCE-E111-B5B0-002564D275D1</Code>
  </CodedValue>
</GPCodedValueDomain2>

I've been trying different iterations of namespace definition for hours, all fail. I'm looking for the output to be:

<GPCodedValueDomain2 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xs="http://www.w3.org/TR/html4/" xmlns:typens="http://www.esri.com/schemas/ArcGIS/10.0" xmlns:type="typens:GPCodedValueDomain2">
  <CodedValue xsi:type="typens:CodedValue">
    <Name>test update</Name>
    <Code xsi:type="xs:string">B59D3BEB-CBCE-E111-B5B0-002564D275D1</Code>
  </CodedValue>
  <CodedValue xsi:type="typens:CodedValue">
    <Name>test update</Name>
    <Code xsi:type="xs:string">B69D3BEB-CBCE-E111-B5B0-002564D275D1</Code>
  </CodedValue>
  <CodedValue xsi:type="typens:CodedValue">
    <Name>test update</Name>
    <Code xsi:type="xs:string">B79D3BEB-CBCE-E111-B5B0-002564D275D1</Code>
  </CodedValue>
</GPCodedValueDomain2>

Any thoughts on how to implement this? Thanks

C. M. Sperberg-McQueen · Accepted Answer · 2012-09-14 01:47:26Z

1

I'm not a SQL Server user, but I wonder if you are really providing a schema document that defines a type named string in the namespace http://www.w3.org/TR/html4/.

There's absolutely nothing wrong with binding the prefix xs to the namespace whose name is the URI of the HTML 4 spec. But several things make me wonder whether you have defined your problem clearly enough.

The HTML 4 spec doesn't define a datatype named string; unless you are doing something very unusual in a schema document not shown here, xs:string is not going to denote a known type.
You appear to be binding the namespace prefix type to the namespace URI typens:GPCodedValueDomain2. That namespace prefix doesn't seem to be used anywhere else, so it's not clear what purpose it's serving. The fact that the URI scheme typens used in that namespace name is also a namespace prefix defined elsewhere in your query suggests that you are either confused about namespace and namespace prefixes, or else that you are doing something extremely subtle and devious. In the latter case, you surely don't need help from Stack Overflow.
You want xsi:type attributes on the CodedValue and Code elements in your output. Your query succeeds in putting the attribute on the first, by specifying SELECT 'typens:CodedValue' AS "@xsi:type", ... for it. But there is no analogous construct for the Code element. Why not?

answered Sep 14, 2012 at 1:47

C. M. Sperberg-McQueen

25.2k6 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tpcolson Over a year ago

The xml is from a column which is initially populated by an application, which performs a good portion of app configuration via xml, of which the posted xml is an example of one. I appreciate your comments, but I'm not doing anything "devious" nor have I invented my own namespace. I'm simply working with what the application outputs, and what it needs in order to read the xml and present the elements/nodes to the applications.

Collectives™ on Stack Overflow

how to prepend xml namespace in element during query?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related