XML Workshop - FOR XML PATH

  • Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/3022.asp

    .

  • I like this. Very cool and useful.

  • Very very informative and good material for explanation..

    Very good

  • Hi,

    Nice article.

    At one stage you say that auto mode does not allow you to specify a root element, and you provide a work around.  Are you perhaps talking about SQL 2000?  As by using the following construct with SQL 2005, you can very simply provide a root element.

    FOR XML AUTO, ROOT ('CustomersByRegion')

    Regards,

    David McKinney.

  • Hi David,

    You are right. Thakx for pointing this.

    Jacob

    .

  • This is very useful, Jacob, and nicely explained. Thanks for that. I loved the format. I find PATH the most intuitive way of generating XML to a precise format. I really struggled before I started using it.

    p.s. microsoft.public.sqlserver.xml can be really useful for  people like me who are still slightly mystified by aspects of XML.

    Best wishes,
    Phil Factor
    Simple Talk

  • Hi phil,

    Thank you for the comments. I am a regular reader of your articles at simple-talk. They are simply excellent :-).

    regards

    Jacob

    .

  • I think PATH is really a great alternative to doing some simple nesting versus EXPLICIT. I'd like to see an article on PATH vs EXPLICIT to see where PATH can be used to supplant EXPLICIT. Good article.

  • Hi charles,

    There is an article on EXPLICIT already on the way. It may be appearing in the next few weeks.

    thankx

    Jacob

    .

  • This is very good information. One other thing I often need, though is to supply namespace declarations in the root or other nodes. How would you add these? I noticed the XSINIL parameter on ELEMENTS adds an XMLSchema-instance namespace.

  • Hi Ryan,

    please post a sample xml structure that you need. I will then try to write a query for that.

    .

  • I'm trying to reproduce something like the following:

    <compensatingcontrols xmlns ="http://www.namespace.net/schema.xsd">
      <compensatingcontrol>
        <id>4</id>
        <name>Control1</name>
        <description />
        <version />
        <source>Source Name</source>
        <controltext>Some control text.</controltext>
        <status>enabled</status>
        <validfrom>2007-08-03</validfrom>
        <expirationflag>neverexpires</expirationflag>
        <expiration>1900-01-01</expiration>
        <lastupdatedby>OWNER</lastupdatedby>
        <lastupdatedon>2007-09-17</lastupdatedon>
        <createdby>OWNER</createdby>
        <createdon>2007-08-03</createdon>
        <additionalinfo>
          <notes xmlns="http://www.namespace.net/schema.xsd" />
        </additionalinfo>
        <ownerinfo>
          <profile>
            <id>3</id>
            <profilename>OWNER</profilename>
          </profile>
        </ownerinfo>
      </compensatingcontrol>
    </compensatingcontrols>

    I can produce everything but the root @xmlns and the notes @xmlns attributes using the following query:

    SELECT compensatingcontrol.ControlID AS 'compensatingcontrol/id'
     , compensatingcontrol.[Control Name] AS 'compensatingcontrol/name'
     , NULL AS 'compensatingcontrol/description'
     , NULL AS 'compensatingcontrol/version'
     , 'START' AS 'compensatingcontrol/source'
     , compensatingcontrol.[Control Description] AS 'compensatingcontrol/controltext'
     , 'compensatingcontrol/status' = 
      CASE compensatingcontrol.[Current?]
       WHEN 'True' THEN 'enabled'
       ELSE 'disabled'
      END
     , '2007-01-01' AS 'compensatingcontrol/validfrom'
     , 'neverexpires' AS 'compensatingcontrol/expirationflag'
     , '1900-01-01' AS 'compensatingcontrol/expiration'
     , 'GRMalone' AS 'compensatingcontrol/lastupdatedby'
     , '2007-06-01' AS 'compensatingcontrol/lastupdatedon'
     , 'GRMalone' AS 'compensatingcontrol/createdby'
     , '2007-01-01' AS 'compensatingcontrol/createdon'
     , NULL AS 'compensatingcontrol/additionalinfo/notes'
     , profile.EmpNum AS 'compensatingcontrol/ownerinfo/profile/id'
     , profile.EmpUserName AS 'compensatingcontrol/ownerinfo/profile/profilename'
    FROM dbo.vwControls compensatingcontrol LEFT OUTER JOIN
     dbo.ProcessControl ownerinfo ON compensatingcontrol.ControlID = ownerinfo.ControlID LEFT OUTER JOIN
     dbo.vwProcessOwners [profile] ON ownerinfo.ProcessID = [profile].ProcessID
    FOR XML PATH('')
     , TYPE
     , ELEMENTS XSINIL
     , ROOT('compensatingcontrols');

    Thanks for your help! These namespaces are really giving me grief.

  • Ryan,

    run this query:

    (excuse the bad formatting)

     

    WITH

    XMLNAMESPACES (

    DEFAULT 'http://www.namespace.net/yourschema.xsd'

    )

    SELECT

    'compensation control ID' AS 'compensatingcontrol/id' ,

    'compensation control name'

    AS 'compensatingcontrol/name' ,

    NULL

    AS 'compensatingcontrol/description' ,

    NULL

    AS 'compensatingcontrol/version' ,

    'START'

    AS 'compensatingcontrol/source' ,

    'compensation control description'

    AS 'compensatingcontrol/controltext' ,

    'true'

    as 'compensatingcontrol/status',

    '2007-01-01'

    AS 'compensatingcontrol/validfrom' ,

    'neverexpires'

    AS 'compensatingcontrol/expirationflag' ,

    '1900-01-01'

    AS 'compensatingcontrol/expiration' ,

    'GRMalone'

    AS 'compensatingcontrol/lastupdatedby' ,

    '2007-06-01'

    AS 'compensatingcontrol/lastupdatedon' ,

    'GRMalone'

    AS 'compensatingcontrol/createdby' ,

    '2007-01-01'

    AS 'compensatingcontrol/createdon' ,

    (

    SELECT 'this is a note'

    FOR XML PATH('notes'), TYPE

    ) as 'compensatingcontrol/additionalinfo',

    --NULL AS 'c:compensatingcontrol/additionalinfo/notes' ,

    'empnum'

    AS 'compensatingcontrol/ownerinfo/profile/id' ,

    'empusername'

    AS 'compensatingcontrol/ownerinfo/profile/profilename'

    FOR

    XML PATH('') , TYPE , ELEMENTS XSINIL , ROOT('compensatingcontrols');

     

    this will give the following results:

    <

    compensatingcontrols xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.namespace.net/yourschema.xsd">

    <

    compensatingcontrol>

    <

    id>compensation control ID</id>

    <

    name>compensation control name</name>

    <

    description xsi:nil="true" />

    <

    version xsi:nil="true" />

    <

    source>START</source>

    <

    controltext>compensation control description</controltext>

    <

    status>true</status>

    <

    validfrom>2007-01-01</validfrom>

    <

    expirationflag>neverexpires</expirationflag>

    <

    expiration>1900-01-01</expiration>

    <

    lastupdatedby>GRMalone</lastupdatedby>

    <

    lastupdatedon>2007-06-01</lastupdatedon>

    <

    createdby>GRMalone</createdby>

    <

    createdon>2007-01-01</createdon>

    <

    additionalinfo>

    <

    notes xmlns="http://www.namespace.net/yourschema.xsd">this is a note</notes>

    </

    additionalinfo>

    <

    ownerinfo>

    <

    profile>

    <

    id>empnum</id>

    <

    profilename>empusername</profilename>

    </

    profile>

    </

    ownerinfo>

    </

    compensatingcontrol>

    </

    compensatingcontrols>

    .

  • In case you are still wondering about this, you can use the "WITH XMLNAMESPACES" clause immediately prior to the SELECT clause, and you can add as many namespaces as you need. An example would be:

    WITH XMLNAMESPACES ('http://www.mynamespaceurl2.com' as "myprefix2", 'http://www.mynamespaceurl1.com' as "myprefix1", DEFAULT 'http://www.mydefaultnamespaceurl.com')

    SELECT...

    You then build the select and sub-select statements, using the prefixes you establish in your namespace clause in the AS clause of each field specification, such as "SELECT Field1 AS 'myprefix1:Field1'" and so on.

    Hope this helps.

  • Hi Jacob,

    How to add a complex element in XML.

    For ex:

    Let say i have my XML like this

    <Business>

    <Business_ID>12345</Business_Id>

    <Account>

    <ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER>

    <ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION>

    <ACCOUNT_TYPE>C</ACCOUNT_TYPE>

    </Account>

    <Account>

    <ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER>

    <ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION>

    <ACCOUNT_TYPE>S</ACCOUNT_TYPE>

    </Account>

    </Business>

    And i want the result to look like this

    <Business>

    <Business_ID>12345</Business_Id>

    <Accounts>

    <Account>

    <ACCOUNT_NUMBER>1122334</ACCOUNT_NUMBER>

    <ACCOUNT_DESCRIPTION>Tesr acct</ACCOUNT_DESCRIPTION>

    <ACCOUNT_TYPE>C</ACCOUNT_TYPE>

    </Account>

    <Account>

    <ACCOUNT_NUMBER>0004455</ACCOUNT_NUMBER>

    <ACCOUNT_DESCRIPTION>Bank Test Account</ACCOUNT_DESCRIPTION>

    <ACCOUNT_TYPE>S</ACCOUNT_TYPE>

    </Account>

    </Accounts>

    </Business>

    I have added here complex type Accounts. Can you tell me how to change my T sql to add this complex element.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply