CODEWITHSUNDEEP
sqlxmlsql-server-2008t-sql
tpcolson
tpcolson

Reputation: 669

how to prepend xml namespace in element during query?

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

Upvotes: 2

Views: 320

Answers (1)

C. M. Sperberg-McQueen
C. M. Sperberg-McQueen

Reputation: 25034

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?

Upvotes: 1

Related Questions