Databases
StackOverflow2013
Postsdbo
POMigrate Table to Sql XML field
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMigrate Table to Sql XML field
    primarykey
    data
    text
    <p>I'm very sorry for this long question! I don't know how to summarize it. The case is very simple but I'm new to SQL XML.</p> <p>I've a table and i want to migrate all records of it to another table with one xml column.</p> <p>Here is my <strong>Field table</strong>:</p> <pre><code>CREATE TABLE [dbo].[Fields] ( [Id] BIGINT IDENTITY (1, 1) NOT NULL, [Title] NCHAR (10) NOT NULL, [Duration] INT NOT NULL, [Cost] MONEY NOT NULL, [Consignee] BIGINT NOT NULL, [Date] DATETIME NOT NULL, [TariffId] BIGINT NOT NULL, [InvoiceType] NCHAR (10) NOT NULL, [IsPayed] BIT NOT NULL ); </code></pre> <p>and this one is my <strong>TypedXML table</strong>:</p> <pre><code>CREATE TABLE [dbo].[TypedXml]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [InvoiceItem] [xml](CONTENT [dbo].[invoiceCollection]) </code></pre> <p>Which have one <strong>schema</strong> collection like this:</p> <pre><code>CREATE XML SCHEMA COLLECTION invoiceCollection AS '&lt;xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.oliol.com" elementFormDefault="qualified" targetNamespace="http://www.oliol.com"&gt; &lt;xsd:element name="Invoice" type="InvoiceType" /&gt; &lt;xsd:complexType name="InvoiceType"&gt; &lt;xsd:sequence&gt; &lt;xsd:element name="Id" type="xsd:long" /&gt; &lt;xsd:element name="Title" type="xsd:string" /&gt; &lt;xsd:element name="Duration" type="xsd:long" /&gt; &lt;xsd:element name="Cost" type="xsd:decimal" /&gt; &lt;xsd:element name="Consignee" type="xsd:long" /&gt; &lt;xsd:element name="Date" type="xsd:dateTime" /&gt; &lt;xsd:element name="TariffId" type="xsd:long" /&gt; &lt;xsd:element name="InvoiceType" type="xsd:string" /&gt; &lt;xsd:element name="IsPayed" type="xsd:int" /&gt; &lt;/xsd:sequence&gt; &lt;/xsd:complexType&gt; &lt;/xsd:schema&gt;' </code></pre> <p>Now, I write this to <strong>migrate</strong>:</p> <pre><code>declare @i bigint set @i=1 while(@i&lt;=10000) begin insert into dbo.TypedXML(invoiceitem) values( (SELECT * FROM Fields where id=1 FOR XML PATH('Invoice'))) set @i=@i+1 End </code></pre> <p>It can not insert, because it try to insert something like this:</p> <p><strong>Insert Failed</strong></p> <pre><code>&lt;Invoice&gt; &lt;Id&gt;1&lt;/Id&gt; &lt;Title&gt;t1&lt;/Title&gt; &lt;Duration&gt;726643700&lt;/Duration&gt; &lt;Cost&gt;312118909727165.6133&lt;/Cost&gt; &lt;Consignee&gt;3120910928797722624&lt;/Consignee&gt; &lt;Date&gt;4543-07-16T01:40:29.623&lt;/Date&gt; &lt;TariffId&gt;3120910928797722624&lt;/TariffId&gt; &lt;InvoiceType&gt;it1&lt;/InvoiceType&gt; &lt;IsPayed&gt;1&lt;/IsPayed&gt; &lt;/Invoice&gt; </code></pre> <p>While I can insert into TypedXML like this:</p> <p><strong>Insert Succeed</strong></p> <pre><code>INSERT typedxml VALUES(' &lt;xml version=1&gt; &lt;Invoice xmlns="http://www.oliol.com"&gt; &lt;Id&gt;1&lt;/Id&gt; &lt;Title&gt;t1&lt;/Title&gt; &lt;Duration&gt;726643700&lt;/Duration&gt; &lt;Cost&gt;312118909727165.6133&lt;/Cost&gt; &lt;Consignee&gt;3120910928797722624&lt;/Consignee&gt; &lt;Date&gt;4543-07-16T01:40:29.623&lt;/Date&gt; &lt;TariffId&gt;3120910928797722624&lt;/TariffId&gt; &lt;InvoiceType&gt;it1&lt;/InvoiceType&gt; &lt;IsPayed&gt;1&lt;/IsPayed&gt; &lt;/Invoice&gt; ') </code></pre> <p>I would like to know how can I change my migration query in order to append <em>xmlns="http://www.oliol.com"</em> to <em>Invoice</em> element?</p> <p><strong>p.s:</strong> I've changed it like this: WITH XMLNAMESPACES ('http://www.oliol.com' as ns) SELECT * FROM Fields where id=1 FOR XML PATH('Invoice') but it's not meet the schema because it produce:</p> <pre><code>&lt;Invoice xmlns:ns="http://www.shaar.com"&gt; &lt;Id&gt;1&lt;/Id&gt; &lt;Title&gt;t1&lt;/Title&gt; &lt;Duration&gt;726643700&lt;/Duration&gt; &lt;Cost&gt;312118909727165.6133&lt;/Cost&gt; &lt;Consignee&gt;3120910928797722624&lt;/Consignee&gt; &lt;Date&gt;4543-07-16T01:40:29.623&lt;/Date&gt; &lt;TariffId&gt;3120910928797722624&lt;/TariffId&gt; &lt;InvoiceType&gt;it1&lt;/InvoiceType&gt; &lt;IsPayed&gt;1&lt;/IsPayed&gt; &lt;/Invoice&gt; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USReza Owliaei
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMigrate Table to Sql XML field
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMigrate Table to Sql XML field
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload