XML Workshop - FOR XML PATH

  • jacob sebastian

    SSChampion

    Points: 11812

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

    .

  • Hemant Chandurkar-412762

    SSC Rookie

    Points: 31

    I like this. Very cool and useful.

  • Intekhab Sheikh

    SSC Rookie

    Points: 31

    Very very informative and good material for explanation..

    Very good

  • David McKinney

    SSChampion

    Points: 10358

    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.

  • jacob sebastian

    SSChampion

    Points: 11812

    Hi David,

    You are right. Thakx for pointing this.

    Jacob

    .

  • Phil Factor

    SSCoach

    Points: 19702

    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

  • jacob sebastian

    SSChampion

    Points: 11812

    Hi phil,

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

    regards

    Jacob

    .

  • charles evans

    SSCommitted

    Points: 1805

    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.

  • jacob sebastian

    SSChampion

    Points: 11812

    Hi charles,

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

    thankx

    Jacob

    .

  • panesofglass

    SSC-Addicted

    Points: 440

    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.

  • jacob sebastian

    SSChampion

    Points: 11812

    Hi Ryan,

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

    .

  • panesofglass

    SSC-Addicted

    Points: 440

    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.

  • jacob sebastian

    SSChampion

    Points: 11812

    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>

    .

  • Doug H-449688

    SSC Journeyman

    Points: 78

    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.

  • Sri..

    SSC Enthusiast

    Points: 137

    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