{"id":122,"date":"2017-09-18T22:30:51","date_gmt":"2017-09-18T22:30:51","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=122"},"modified":"2020-05-19T21:13:54","modified_gmt":"2020-05-19T21:13:54","slug":"tablas-datos-xml","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=122","title":{"rendered":"Create table con datos tipo XML SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML00.png\" alt=\"\" class=\"wp-image-1152\" width=\"241\" height=\"243\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML00.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML00-297x300.png 297w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML00-150x150.png 150w\" sizes=\"auto, (max-width: 241px) 100vw, 241px\" \/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Uso de campos XML en tablas en SQL Server<\/strong><\/h1>\n\n\n\n<p>Es posible incluir en un campo de una tabla datos en formato XML (eXtensible Markup Language), el tipo de datos XML permite almacenar datos que dependan del registro en la misma tabla y posiblemente evitar dise\u00f1ar un maestro &#8211; detalle.<\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Ejemplo<\/strong><\/h2>\n\n\n\n<p>En una cl\u00ednica veterinaria se podr\u00eda guardar los datos de las mascotas de un determinado propietario en una sola tabla, es decir, Propietarios y Mascotas en la misma tabla. En el c\u00f3digo se muestra la inserci\u00f3n de registros de manera manual, es necesario de preferencia usar alg\u00fan editor de archivos XML.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Tabla para Propietarios<\/strong><\/h3>\n\n\n\n<p>Create table Propietarios<br>(<br>PropietariosCodigo nchar(10) not null ,<br>PropietariosNombre nvarchar(200) not null,<br>Mascotas XML,<br>PropietariosTelefono nvarchar(30),<br>constraint PropietariosPk primary key (PropietariosCodigo)<br>)<br>go<\/p>\n\n\n\n<p><strong>Al insertar los datos del Propietario, se pueden insertar los datos de sus mascotas en el campo Mascotas de tipo XML.<\/strong><\/p>\n\n\n\n<p>Insert into Propietarios<br>(PropietariosCodigo, PropietariosNombre, Mascotas, PropietariosTelefono)<br>values<br>(&#8216;85214&#8242;,&#8217;CARLOS MONTENEGRO&#8217;,'&lt;Mascotas&gt;<br>&lt;Mascota&gt;<br>&lt;Codigo&gt;1285&lt;\/Codigo&gt;<br>&lt;Descripcion&gt;Gato&lt;\/Descripcion&gt;<br>&lt;Nombre&gt;Garfield&lt;\/Nombre&gt;<br>&lt;FechaNacimiento&gt;15\/07\/2015&lt;\/FechaNacimiento&gt;<br>&lt;\/Mascota&gt;<br>&lt;Mascota&gt;<br>&lt;Codigo&gt;89658&lt;\/Codigo&gt;<br>&lt;Descripcion&gt;Perro&lt;\/Descripcion&gt;<br>&lt;Nombre&gt;Cody&lt;\/Nombre&gt;<br>&lt;FechaNacimiento&gt;18\/09\/2005&lt;\/FechaNacimiento&gt;<br>&lt;\/Mascota&gt;<br>&lt;\/Mascotas&gt;&#8217;,&#8217;948635251&#8242;),<br>(&#8216;74965&#8242;,&#8217;LUIS VILLANUEVA&#8217;,'&lt;Mascotas&gt;<br>&lt;Mascota&gt;<br>&lt;Codigo&gt;9625&lt;\/Codigo&gt;<br>&lt;Descripcion&gt;Gallo&lt;\/Descripcion&gt;<br>&lt;Nombre&gt;Aristides&lt;\/Nombre&gt;<br>&lt;FechaNacimiento&gt;05\/04\/1992&lt;\/FechaNacimiento&gt;<br>&lt;\/Mascota&gt;<br>&lt;Mascota&gt;<br>&lt;Codigo&gt;89345&lt;\/Codigo&gt;<br>&lt;Descripcion&gt;Loro&lt;\/Descripcion&gt;<br>&lt;Nombre&gt;Betito&lt;\/Nombre&gt;<br>&lt;FechaNacimiento&gt;18\/09\/2005&lt;\/FechaNacimiento&gt;<br>&lt;\/Mascota&gt;<br>&lt;Mascota&gt;<br>&lt;Codigo&gt;4545&lt;\/Codigo&gt;<br>&lt;Descripcion&gt;Rata&lt;\/Descripcion&gt;<br>&lt;Nombre&gt;Romulo Alan&lt;\/Nombre&gt;<br>&lt;FechaNacimiento&gt;08\/10\/2005&lt;\/FechaNacimiento&gt;<br>&lt;\/Mascota&gt;<br>&lt;\/Mascotas&gt;&#8217;,&#8217;01-252858&#8242;)<br>go<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><a href=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_CamposXML01.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML01-1024x145.png\" alt=\"\" class=\"wp-image-1153\" width=\"787\" height=\"111\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML01-1024x145.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML01-300x43.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML01-768x109.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML01.png 1366w\" sizes=\"auto, (max-width: 787px) 100vw, 787px\" \/><\/a><\/figure><\/div>\n\n\n\n<p>Al pulsar sobre el campo XML del primer registro de LUIS VILLANUEVA se muestre el detalle del campo XML.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large\"><a href=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_CamposXML02.png\"><img loading=\"lazy\" decoding=\"async\" width=\"770\" height=\"594\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML02.png\" alt=\"\" class=\"wp-image-1154\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML02.png 770w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML02-300x231.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML02-768x592.png 768w\" sizes=\"auto, (max-width: 770px) 100vw, 770px\" \/><\/a><\/figure><\/div>\n\n\n\n<p>Las tablas con campos de tipo XML se pueden listar incluyendo todos los datos en un formato de salida XML<\/p>\n\n\n\n<p><strong>select * from Propietarios for XML auto<\/strong><br><strong>go<\/strong><\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><a href=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_CamposXML03.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML03.png\" alt=\"\" class=\"wp-image-1155\" width=\"880\" height=\"692\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML03.png 805w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML03-300x236.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML03-768x605.png 768w\" sizes=\"auto, (max-width: 880px) 100vw, 880px\" \/><\/a><figcaption>Modo auto en el listado de una tabla en formato XML<\/figcaption><\/figure><\/div>\n\n\n\n<p><strong>select * from Propietarios for XML raw<\/strong><br><strong>go<\/strong><\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter size-large is-resized\"><a href=\"http:\/\/www.manualsqlserver.com\/wp-content\/uploads\/2017\/05\/ManualSQL_CamposXML04.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML04.png\" alt=\"\" class=\"wp-image-1156\" width=\"840\" height=\"697\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML04.png 766w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_CamposXML04-300x249.png 300w\" sizes=\"auto, (max-width: 840px) 100vw, 840px\" \/><\/a><\/figure><\/div>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Uso de campos XML en tablas en SQL Server Es posible incluir en un campo de una tabla datos en formato XML (eXtensible Markup Language), el tipo de datos XML permite almacenar datos que dependan del registro en la misma tabla y posiblemente evitar dise\u00f1ar un maestro &#8211; detalle.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=122\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1152,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[20,28,46,47,57,95],"class_list":["post-122","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-esquemastablas","tag-create-table","tag-esquemas","tag-particion","tag-permisos","tag-tablas","tag-xml","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/122","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=122"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/122\/revisions"}],"predecessor-version":[{"id":1158,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/122\/revisions\/1158"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1152"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=122"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=122"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=122"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}