Select values from XML field in SQL Server 2008

Question

Just looking at my XML field  my rows look like this    lt person gt  lt firstName gt Jon lt  firstName gt  lt lastName gt Johnson lt  lastName gt  lt  person gt   lt person gt  lt firstName gt Kathy lt  firstName gt  lt lastName gt Carter lt  lastName gt  lt  person gt   lt person gt  lt firstName gt Bob lt  firstName gt  lt lastName gt Burns lt  lastName gt  lt  person gt    Note that these are three rows in my table   I d like to return a SQL result as a table as in  Jon    Johnson Kathy  Carter Bob    Burns   What query will accomplish this

User · Answer

Blimey   This was a really useful thread to discover   I still found some of these suggestions confusing   Whenever I used value with  1  in the string  it would only retrieved the first value   And some suggestions recommended using cross apply which  in my tests  just brought back far too much data   So  here s my simple example of how you d create an xml object  then read out its values into a table   DECLARE  str nvarchar 2000   SET  str      SET  str    str     lt users gt   SET  str    str       lt user gt   SET  str    str          lt firstName gt Mike lt  firstName gt   SET  str    str          lt lastName gt Gledhill lt  lastName gt   SET  str    str          lt age gt 31 lt  age gt   SET  str    str       lt  user gt   SET  str    str       lt user gt   SET  str    str          lt firstName gt Mark lt  firstName gt   SET  str    str          lt lastName gt Stevens lt  lastName gt   SET  str    str          lt age gt 42 lt  age gt   SET  str    str       lt  user gt   SET  str    str       lt user gt   SET  str    str          lt firstName gt Sarah lt  firstName gt   SET  str    str          lt lastName gt Brown lt  lastName gt   SET  str    str          lt age gt 23 lt  age gt   SET  str    str       lt  user gt   SET  str    str     lt  users gt    DECLARE  xml xml SELECT  xml   CAST CAST  str AS VARBINARY MAX   AS XML    --  Iterate through each of the  users user  records in our XML SELECT      x Rec query    firstName   value       nvarchar 2000    AS  FirstName       x Rec query    lastName   value       nvarchar 2000    AS  LastName       x Rec query    age   value       int   AS  Age  FROM  xml nodes   users user   as x Rec    And here s the output     It s bizarre syntax  but with a decent example  it s easy enough to add to your own SQL Server functions   Speaking of which  here s the correct answer to this question   Assuming your have your xml data in an  xml variable of type xml  as demonstrated in my example above   here s how you would return the three rows of data from the xml quoted in the question   SELECT      x Rec query    firstName   value       nvarchar 2000    AS  FirstName       x Rec query    lastName   value       nvarchar 2000    AS  LastName  FROM  xml nodes   person   as x Rec

User · Answer

This may answer your question   select cast xmlField as xml  xmlField into tmp from   select   lt person gt  lt firstName gt Jon lt  firstName gt  lt lastName gt Johnson lt  lastName gt  lt  person gt   xmlField union select   lt person gt  lt firstName gt Kathy lt  firstName gt  lt lastName gt Carter lt  lastName gt  lt  person gt   union select   lt person gt  lt firstName gt Bob lt  firstName gt  lt lastName gt Burns lt  lastName gt  lt  person gt     tb  SELECT     xmlField value   person firstName  1     nvarchar max    as FirstName      xmlField value   person lastName  1     nvarchar max    as LastName FROM tmp  drop table tmp

User · Answer

If you are able to wrap your XML in a root element - say  then the following is your solution   DECLARE  PersonsXml XML     lt persons gt  lt person gt  lt firstName gt Jon lt  firstName gt  lt lastName gt Johnson lt  lastName gt  lt  person gt   lt person gt  lt firstName gt Kathy lt  firstName gt  lt lastName gt Carter lt  lastName gt  lt  person gt   lt person gt  lt firstName gt Bob lt  firstName gt  lt lastName gt Burns lt  lastName gt  lt  person gt  lt  persons gt    SELECT  b value     firstName text    1    nvarchar max    as FirstName  b value     lastName text    1    nvarchar max    as LastName FROM  PersonsXml nodes   persons person   AS a b

User · Answer

MSSQL uses regular XPath rules as follows    nodename     Selects all nodes with the name  nodename       Selects from the root node      Selects nodes in the document from the current node that match the selection no matter where they are      Selects the current node      Selects the parent of the current node      Selects attributes   W3Schools

User · Answer

This example uses an XML variable with a schema     IF EXISTS  SELECT   FROM sys xml schema collections             WHERE name    OrderingAfternoonTea   BEGIN     DROP XML SCHEMA COLLECTION dbo OrderingAfternoonTea  END GO  CREATE XML SCHEMA COLLECTION dbo OrderingAfternoonTea AS N  lt  xml version  1 0  encoding  UTF-16    gt     lt xsd schema xmlns xsd  http   www w3 org 2001 XMLSchema       targetNamespace  http   Tfor2 com schemas actions orderAfternoonTea       xmlns  http   Tfor2 com schemas actions orderAfternoonTea       xmlns TFor2  http   Tfor2 com schemas actions orderAfternoonTea       elementFormDefault  qualified       version  0 10      gt        lt xsd complexType name  AfternoonTeaOrderType  gt          lt xsd sequence gt            lt xsd element name  potsOfTea  type  xsd int   gt            lt xsd element name  cakes  type  xsd int   gt            lt xsd element name  fruitedSconesWithCream  type  xsd int   gt            lt xsd element name  jams  type  xsd string   gt         lt  xsd sequence gt         lt xsd attribute name  schemaVersion  type  xsd long  use  required   gt       lt  xsd complexType gt        lt xsd element name  afternoonTeaOrder                   type  TFor2 AfternoonTeaOrderType   gt      lt  xsd schema gt     GO  DECLARE  potsOfTea int  DECLARE  cakes int  DECLARE  fruitedSconesWithCream int  DECLARE  jams nvarchar 128    DECLARE  RequestMsg NVARCHAR 2048   DECLARE  RequestXml XML dbo OrderingAfternoonTea    set  potsOfTea   5  set  cakes   7  set  fruitedSconesWithCream   25  set  jams   N medlar jelly  quince and mulberry    SELECT  RequestMsg   N  lt  xml version  1 0  encoding  utf-16    gt   lt TFor2 afternoonTeaOrder schemaVersion  10      xmlns TFor2  http   Tfor2 com schemas actions orderAfternoonTea  gt       lt TFor2 potsOfTea gt     CAST  potsOfTea as NVARCHAR 20                lt  TFor2 potsOfTea gt       lt TFor2 cakes gt     CAST  cakes as NVARCHAR 20       lt  TFor2 cakes gt       lt TFor2 fruitedSconesWithCream gt              CAST  fruitedSconesWithCream as NVARCHAR 20               lt  TFor2 fruitedSconesWithCream gt       lt TFor2 jams gt      jams     lt  TFor2 jams gt   lt  TFor2 afternoonTeaOrder gt     SELECT  RequestXml    CAST CAST  RequestMsg AS VARBINARY MAX   AS XML     with xmlnamespaces  http   Tfor2 com schemas actions orderAfternoonTea                      as tea  select     cast  x Rec value    1   schemaVersion   nvarchar 20    as bigint           as schemaVersion      cast  x Rec query    tea potsOfTea                   value      nvarchar 20    as bigint   as potsOfTea      cast  x Rec query    tea cakes                   value      nvarchar 20    as bigint    as cakes      cast  x Rec query    tea fruitedSconesWithCream                   value      nvarchar 20    as bigint          as fruitedSconesWithCream      x Rec query    tea jams   value      nvarchar 50     as jams from  RequestXml nodes   tea afternoonTeaOrder    as x Rec    select  RequestXml query

User · Answer

Considering that XML data comes from a table  table  and is stored in a column  field   use the XML methods  extract values with xml value    project nodes with xml nodes    use CROSS APPLY to join    SELECT      p value     firstName  1     VARCHAR 8000    AS firstName      p value     lastName  1     VARCHAR 8000    AS lastName FROM table      CROSS APPLY field nodes   person   t p    You can ditch the nodes   and cross apply if each field contains exactly one element  person   If the XML is a variable you select FROM  variable nodes      and you don t need the cross apply

User · Answer

This post was helpful to solve my problem which has a little different XML format     my XML contains a list of keys like the following example and I store the XML in the SourceKeys column in a table named DeleteBatch    lt k gt 1 lt  k gt   lt k gt 2 lt  k gt   lt k gt 3 lt  k gt    Create the table and populate it with some data   CREATE TABLE dbo DeleteBatch       ExecutionKey INT PRIMARY KEY      SourceKeys XML   INSERT INTO dbo DeleteBatch   ExecutionKey  SourceKeys   SELECT 1        CAST   lt k gt 1 lt  k gt  lt k gt 2 lt  k gt  lt k gt 3 lt  k gt   AS XML    INSERT INTO dbo DeleteBatch   ExecutionKey  SourceKeys   SELECT 2        CAST   lt k gt 100 lt  k gt  lt k gt 101 lt  k gt   AS XML     Here s my SQL to select the keys from the XML   SELECT ExecutionKey  p value       int   AS  Key  FROM dbo DeleteBatch     CROSS APPLY SourceKeys nodes   k   t p    Here s the query results      ExecutionKey    Key 1   1 1   2 1   3 2   100 2   101

User · Answer

Given that the XML field is named  xmlField      SELECT   xmlField  value    person  firstName node    1     nvarchar max    as FirstName   xmlField  value    person  lastName node    1     nvarchar max    as LastName FROM  myTable

User · Answer

SELECT  cast xmlField as xml  value    person  firstName node    1     nvarchar max    as FirstName  cast xmlField as xml  value    person  lastName node    1     nvarchar max    as LastName FROM  myTable

[sql-server] Select values from XML field in SQL Server 2008

Examples related to sql-server

Examples related to xml

Examples related to xpath