Post reply

XML Workshop - FOR XML PATH

  • May 22, 2007 at 10:55 am

    #153599

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

    .

  • June 19, 2007 at 10:33 pm

    #714000

    I like this. Very cool and useful.

  • June 20, 2007 at 12:34 am

    #714010

    Very very informative and good material for explanation..

    Very good

  • June 20, 2007 at 1:03 am

    #714014

    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.

  • June 20, 2007 at 1:19 am

    #714017

    Hi David,

    You are right. Thakx for pointing this.

    Jacob

    .

  • June 20, 2007 at 2:53 am

    #714035

    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

  • June 20, 2007 at 3:07 am

    #714038

    Hi phil,

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

    regards

    Jacob

    .

  • June 20, 2007 at 3:07 pm

    #714223

    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.

  • June 20, 2007 at 11:53 pm

    #714250

    Hi charles,

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

    thankx

    Jacob

    .

  • September 20, 2007 at 3:15 pm

    #735078

    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.

  • September 20, 2007 at 11:07 pm

    #735115

    Hi Ryan,

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

    .

  • September 21, 2007 at 6:48 am

    #735205

    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.

  • September 21, 2007 at 11:42 pm

    #735422

    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>

    .

  • March 20, 2008 at 10:51 am

    #792133

    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.

  • April 19, 2010 at 10:43 am

    #1153981

    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 21 total)

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