create a XML for two tables hierarchy

Question

can any one share a script please

i have these two table , tbl header adn tbl detail, how to create a xml file ?

Answer 1

DilipKumar,

Try

select * from Header    FOR XML AUTO, ELEMENTS
select * from Details    FOR XML AUTO, ELEMENTS

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

it creates separate xmls, i want in the same xml

Answer 3

Have a look  http://stackoverflow.com/questions/6113538/output-nested-xml-in-sql-server-2008

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

i am looking for some thing like element complex and sequence

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="note">
  <xs:complexType>
    <xs:sequence>
      <xs:element name="to" type="xs:string"/>
      <xs:element name="from" type="xs:string"/>
      <xs:element name="heading" type="xs:string"/>
      <xs:element name="body" type="xs:string"/>
    </xs:sequence>
  </xs:complexType>
</xs:element>

</xs:schema>

Answer 5

For one XML document, you could combine the previous queries like...

 

select *
 ,(select * from Details
   WHERE agency_id = hdr.agency_id -- or other criteria
   FOR XML PATH('Detail') ,ROOT('Details') ,TYPE
  )
from Header hdr
FOR XML PATH('Header') ,ROOT('Headers') ,TYPE

---

Birddeaux

Answer 6

@ icq63652861 that was very helpful ,

i am looking at a sample xml file and trying to generate my own sample file based on other tables , how to obtain the first three lines and what does SCh and XSI  come using the query

<sch:TCSBatchRequest xmlns:sch="http://f.tr/schemas" 
                     xmlns:xsi="http://www.w3.pen/5002/XMLSchema-instance" 
                     xsi:schemaLocation="http://f.tr/schemas tcs_batch.xsd">
<sch:agency_id>944</sch:agency_id>
<sch:tcs_app_id>2601</sch:tcs_app_id>
<sch:app_batch_id>10120710470300000</sch:app_batch_id>
<sch:net_amount>19.36</sch:net_amount>
<sch:total_amount>19.36</sch:total_amount>
<sch:transaction_count>2</sch:transaction_count>
<sch:Batch_Transactions>
<sch:ACHDebitRequest>
<sch:agency_id>944</sch:agency_id>
<sch:tcs_app_id>2601</sch:tcs_app_id>
<sch:ACHDebit>
<sch:agency_tracking_id>10120710470300001</sch:agency_tracking_id>
<sch:account_number>4368060</sch:account_number>
<sch:routing_transit_number>231371841</sch:routing_transit_number>
<sch:account_type>BusinessChecking</sch:account_type>
<sch:transaction_amount>6.33</sch:transaction_amount>
<sch:payment_options>
<sch:payment_frequency>OneTime</sch:payment_frequency>
<sch:payment_date>2010-12-08</sch:payment_date>
</sch:payment_options>
<sch:first_name>David</sch:first_name>
<sch:middle_initial />
<sch:last_name>Soul</sch:last_name>
<sch:check_number>88277880</sch:check_number>
<sch:sec_code>WEB</sch:sec_code>
<sch:custom_fields>
<sch:custom_field_1>10evlaChoutr5eBrIedROeStL4bRiU</sch:custom_field_1>
<sch:custom_field_2>1IeVo9dR2AwrlAZluViEtHl53i03ia</sch:custom_field_2>
</sch:custom_fields>
<sch:account_holder_email_address>[e-mail]</sch:account_holder_email_address>
</sch:ACHDebit>
</sch:ACHDebitRequest>
<sch:PCSaleRequest>
<sch:agency_id>944</sch:agency_id>
<sch:tcs_app_id>2601</sch:tcs_app_id>
<sch:PCSale>
<sch:agency_tracking_id>10120710470300002</sch:agency_tracking_id>
<sch:transaction_amount>13.03</sch:transaction_amount>
<sch:account_number>341111597242000</sch:account_number>
<sch:credit_card_expiration_date>2013-03</sch:credit_card_expiration_date>
<sch:first_name>David</sch:first_name>
<sch:middle_initial />
<sch:last_name>Soul</sch:last_name>
<sch:billing_address>BillingAddress</sch:billing_address>
<sch:billing_address_2>BillingAddress2</sch:billing_address_2>
<sch:billing_city>BillingCity</sch:billing_city>
<sch:billing_state>OH</sch:billing_state>
<sch:billing_zip>11111</sch:billing_zip>
<sch:billing_country>840</sch:billing_country>
<sch:order_id />
<sch:order_tax_amount />
<sch:custom_fields>
<sch:custom_field_1>218prLU1Rl3cOa3hletRle1hienIat</sch:custom_field_1>
<sch:custom_field_2>32eFria1rOEYoAChoacRLamOEbOUn6</sch:custom_field_2>
</sch:custom_fields>
<sch:account_holder_email_address>[e-mail]</sch:account_holder_email_address>
</sch:PCSale>
</sch:PCSaleRequest>
</sch:Batch_Transactions>
</sch:TCSBatchRequest>

can you please suggest how to write a query to get those first 4 lines of XML..please share your code

 

Answer 7

Try to create a temp/hash table #tmp as per your xml structure. Hope this will help you