Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Answered: adjust namespaces in SQL generated XML

    Hi,

    I'm having trouble getting the namespaces correct in the XML that is generated by SQL. The XML consists of two parts: a header part with some context information and the payload part with the actual data. With my current SQL script, the namespace is also copied to the payload part.
    Here are some DDL and DML scripts to generate two test tables and some test data:
    Code:
    CREATE TABLE dbo.context(
    	id		int		NOT NULL	constraint pk_dbo_context primary key,
    	name	nvarchar(100)	NOT NULL
    )
    
    INSERT INTO dbo.context(id, name) VALUES (1, 'Here comes some great context information.')
    
    CREATE TABLE dbo.content(
    	id	int	identity	NOT NULL	constraint pk_context primary key,
    	context_id	int	NOT NULL foreign key references dbo.context(id),
    	question_attribute_1	nvarchar(100)	NOT NULL,
    	question_attribute_2	nvarchar(100)	NOT NULL
    )
    
    INSERT INTO dbo.content(context_id, question_attribute_1, question_attribute_2) VALUES
    (1, 'record1 attribute 1', 'record1 attribute 2'),
    (1, 'record2 attribute 1', 'record2 attribute 2')
    This is my current FOR XML script that generates the XML:
    Code:
    ;WITH XMLNAMESPACES (DEFAULT 'http://services.registersubscription-02_00.a.cool.url.com')
    SELECT 
    	  CEXT.name AS [Context/Name],
    	  (SELECT 
    			CONT.id AS [Content/Reference],
    			CONT.question_attribute_1 AS [Content/Attrib1],
    			CONT.question_attribute_2 AS [Content/Attrib2]
    		FROM dbo.content as CONT
    		WHERE CONT.context_id = CEXT.id
    		FOR XML PATH ('Question'), ROOT ('Questions'), TYPE
    		)
    FROM dbo.context as CEXT
    WHERE CEXT.id = 1
    FOR XML PATH('Request'), ROOT('RegisterSubscription');
    This generates this XML (notice that the namespace is repetated in the <Questions> element):
    Code:
    <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com">
      <Request>
        <Context>
          <Name>Here comes some great context information.</Name>
        </Context>
        <Questions xmlns="http://services.registersubscription-02_00.a.cool.url.com">
          <Question>
            <Content>
              <Reference>1</Reference>
              <Attrib1>record1 attribute 1</Attrib1>
              <Attrib2>record1 attribute 2</Attrib2>
            </Content>
          </Question>
          <Question>
            <Content>
              <Reference>2</Reference>
              <Attrib1>record2 attribute 1</Attrib1>
              <Attrib2>record2 attribute 2</Attrib2>
            </Content>
          </Question>
        </Questions>
      </Request>
    </RegisterSubscription>
    The XML should be like this:
    Code:
    <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com">
      <Request xmlns="">
        <Context>
          <Name>Here comes some great context information.</Name>
        </Context>
        <Questions>
          <Question>
            <Content>
              <Reference>1</Reference>
              <Attrib1>record1 attribute 1</Attrib1>
              <Attrib2>record1 attribute 2</Attrib2>
            </Content>
          </Question>
          <Question>
            <Content>
              <Reference>2</Reference>
              <Attrib1>record2 attribute 1</Attrib1>
              <Attrib2>record2 attribute 2</Attrib2>
            </Content>
          </Question>
        </Questions>
      </Request>
    </RegisterSubscription>
    The generated XML does not pass the XSD validation. After Googling, I found out that I'm not the only one with this kind of problem.
    Last edited by Wim; 05-22-15 at 06:50.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. Best Answer
    Posted by Wim

    "I found a solution:
    Code:
    DECLARE @Xml xml
    SET @Xml = (SELECT 
    				  CEXT.name AS [Context/Name],
    				  (SELECT 
    						CONT.id AS [Content/Reference],
    						CONT.question_attribute_1 AS [Content/Attrib1],
    						CONT.question_attribute_2 AS [Content/Attrib2]
    					FROM dbo.content as CONT
    					WHERE CONT.context_id = CEXT.id
    					FOR XML PATH ('Question'), ROOT ('Questions'), TYPE
    					)
    			FROM dbo.context as CEXT
    			WHERE CEXT.id = 1
    			FOR XML PATH('Request')
    			);
    WITH XMLNAMESPACES (DEFAULT 'http://services.registersubscription-02_00.a.cool.url.com')
    SELECT  @Xml FOR XML PATH('RegisterSubscription');
    This is the result:
    Code:
    <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com">
      <Request xmlns="">
        <Context>
          <Name>Here comes some great context information.</Name>
        </Context>
        <Questions>
          <Question>
            <Content>
              <Reference>1</Reference>
              <Attrib1>record1 attribute 1</Attrib1>
              <Attrib2>record1 attribute 2</Attrib2>
            </Content>
          </Question>
          <Question>
            <Content>
              <Reference>2</Reference>
              <Attrib1>record2 attribute 1</Attrib1>
              <Attrib2>record2 attribute 2</Attrib2>
            </Content>
          </Question>
        </Questions>
      </Request>
    </RegisterSubscription>
    "


  3. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I found a solution:
    Code:
    DECLARE @Xml xml
    SET @Xml = (SELECT 
    				  CEXT.name AS [Context/Name],
    				  (SELECT 
    						CONT.id AS [Content/Reference],
    						CONT.question_attribute_1 AS [Content/Attrib1],
    						CONT.question_attribute_2 AS [Content/Attrib2]
    					FROM dbo.content as CONT
    					WHERE CONT.context_id = CEXT.id
    					FOR XML PATH ('Question'), ROOT ('Questions'), TYPE
    					)
    			FROM dbo.context as CEXT
    			WHERE CEXT.id = 1
    			FOR XML PATH('Request')
    			);
    WITH XMLNAMESPACES (DEFAULT 'http://services.registersubscription-02_00.a.cool.url.com')
    SELECT  @Xml FOR XML PATH('RegisterSubscription');
    This is the result:
    Code:
    <RegisterSubscription xmlns="http://services.registersubscription-02_00.a.cool.url.com">
      <Request xmlns="">
        <Context>
          <Name>Here comes some great context information.</Name>
        </Context>
        <Questions>
          <Question>
            <Content>
              <Reference>1</Reference>
              <Attrib1>record1 attribute 1</Attrib1>
              <Attrib2>record1 attribute 2</Attrib2>
            </Content>
          </Question>
          <Question>
            <Content>
              <Reference>2</Reference>
              <Attrib1>record2 attribute 1</Attrib1>
              <Attrib2>record2 attribute 2</Attrib2>
            </Content>
          </Question>
        </Questions>
      </Request>
    </RegisterSubscription>
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •