Query Data from Xml

How to select/query xml data from element and selecting sibling value in SQL

What you are looking for is a XQuery predicate and how to stuff a value into your XQuery. The first needs brackets ([]), the second can be achieved with the function sql:variable():

Try it like this:

DECLARE @YourTable TABLE(ID INT IDENTITY,[Data] XML);
INSERT INTO @YourTable VALUES
(N'<ArrayOfDataAttribute xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<DataAttribute>
    <Name>field1</Name>
    <Value>default-value-example</Value>
</DataAttribute>
<DataAttribute>
    <Name>field2</Name>
</DataAttribute>
<DataAttribute>
    <Name>field5</Name>
    <Value>False</Value>
</DataAttribute>
<DataAttribute>
    <Name>field4</Name>
    <Value>example value</Value>
</DataAttribute>
<DataAttribute>
    <Name>field5</Name>
    <Value>another value</Value>
</DataAttribute>
</ArrayOfDataAttribute>');

--A parameter for your search string

DECLARE @searchFor NVARCHAR(MAX)=N'field4'

--the query

SELECT t.ID
      ,t.[Data].value(N'(/ArrayOfDataAttribute
                         /DataAttribute[(Name/text())[1]=sql:variable("@searchFor")]
                         /Value
                         /text())[1]',N'nvarchar(max)') AS field4
FROM @YourTable t;

The XPath can be read as:

Dive into the array of attributes and look for the <DataAttribute> with a <Name> of a given value. There we need the text() within Value.

Hint: Although there are namespaces, the given sample makes no use of any of them. We can omit the declaration in this case...

How to query for Xml values and attributes from table in SQL Server?

Actually you're close to your goal, you just need to use nodes() method to split your rows and then get values:

select
    s.SqmId,
    m.c.value('@id', 'varchar(max)') as id,
    m.c.value('@type', 'varchar(max)') as type,
    m.c.value('@unit', 'varchar(max)') as unit,
    m.c.value('@sum', 'varchar(max)') as [sum],
    m.c.value('@count', 'varchar(max)') as [count],
    m.c.value('@minValue', 'varchar(max)') as minValue,
    m.c.value('@maxValue', 'varchar(max)') as maxValue,
    m.c.value('.', 'nvarchar(max)') as Value,
    m.c.value('(text())[1]', 'nvarchar(max)') as Value2
from sqm as s
    outer apply s.data.nodes('Sqm/Metrics/Metric') as m(c)

sql fiddle demo

Query Out XML Data

Assuming it is SQL Server.

XQuery .nodes() and .value() methods produce what you need.

You may need to adjust data types beyond INT.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<FIELD>
    <ROW>
        <FIELD name="LI_PRODID">1</FIELD>
        <FIELD name="LI_QTY">3</FIELD>
        <FIELD name="CALC_UOM">1</FIELD>
    </ROW>
    <ROW>
        <FIELD name="LI_PRODID">2</FIELD>
        <FIELD name="LI_QTY">4</FIELD>
        <FIELD name="CALC_UOM">1</FIELD>
    </ROW>
</FIELD>');
-- DDL and sample data population, end

SELECT c.value('(FIELD[@name="LI_PRODID"]/text())[1]', 'INT') AS LI_PRODID
    , c.value('(FIELD[@name="LI_QTY"]/text())[1]', 'INT') AS LI_QTY
    , c.value('(FIELD[@name="CALC_UOM"]/text())[1]', 'INT') AS CALC_UOM
FROM @tbl CROSS APPLY xmldata.nodes('/FIELD/ROW') AS t(c);

Output

+-----------+--------+----------+
| LI_PRODID | LI_QTY | CALC_UOM |
+-----------+--------+----------+
|         1 |      3 |        1 |
|         2 |      4 |        1 |
+-----------+--------+----------+

Extracting data from xml into SQL server table

Here's a super-duper hack that's not limited to a set number of "columns":

-- Data mock-up.
DECLARE @value VARCHAR(500) = '[My company customer detail - Account ID <3116131311616116>, Subscriber Name <Jon>, Age <52>, Phone <>, Payment<CC>]';

-- Construct the dynamic SQL field/value list.
DECLARE @sql VARCHAR(MAX);
SELECT @sql = ISNULL( @sql, '' ) 
    + ', ' + QUOTENAME( SUBSTRING( value, CHARINDEX( '<', value ) + 1, CHARINDEX( '>', value ) - CHARINDEX( '<', value ) - 1 ), '''' )
    + ' AS [' + LTRIM( RTRIM( LEFT( value, CHARINDEX( '<', value ) - 1 ) ) ) + ']'
FROM STRING_SPLIT( REPLACE( REPLACE( @value, ']', '' ), '[', '' ), ',' )

-- Complete the dynamic SQL.
SET @sql = 'SELECT ' + STUFF( @sql, 1, 2, '' ) + ';';

-- Print the resulting dynamic SQL.
PRINT @sql;

-- Execute the dynamic SQL.
EXEC( @sql );

PRINT displays:

SELECT '3116131311616116' AS [My company customer detail - Account ID], 'Jon' AS [Subscriber Name], '52' AS [Age], '' AS [Phone], 'CC' AS [Payment];

EXEC returns:

+-----------------------------------------+-----------------+-----+-------+---------+
| My company customer detail - Account ID | Subscriber Name | Age | Phone | Payment |
+-----------------------------------------+-----------------+-----+-------+---------+
|                        3116131311616116 | Jon             |  52 |       | CC      |
+-----------------------------------------+-----------------+-----+-------+---------+

Query XML data for information based on a sibling value

Hi perhaps try a sibling match

/Container/Collection/ItemName[../ExistsInDB='true']

That gets ItemName elements whose parents contain an ExistsInDb child equal to "true".

DECLARE @xml xml = '
<Container>
  <Collection>
    <ItemName>SomeItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>false</IsPersisted>
  </Collection>
  <Collection>
    <ItemName>AnotherItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>true</ExistsInDB>
  </Collection>
  <Collection>
    <ItemName>ItemFoo</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>true</ExistsInDB>
  </Collection>
  <Collection>
    <ItemName>BarBazItem</ItemName>
    <IsDeletable>true</IsDeletable>
    <IsPersisted>true</IsPersisted>
    <ExistsInDB>false</ExistsInDB>
  </Collection>
</Container>
';

SELECT node.value('.', 'nvarchar(100)')
FROM @xml.nodes('/Container/Collection/ItemName[../ExistsInDB="true"]') AS x(node)

Get all values from an XML column

You did not state your expected output clearly, but this seems to be rather easy. XML can deal with generic structures quite well:

DECLARE @xml XML=
'<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>';

--The query will use .nodes() with a path to /*.

--This will return all elements below <Fields>, however they are named

--The query will return the element's name (local-name(.)) together with the two nested elements for old and new value:

SELECT fld.value('local-name(.)','nvarchar(max)') AS FieldName
      ,fld.value('(OldValue/text())[1]','nvarchar(max)') AS OldValue
      ,fld.value('(NewValue/text())[1]','nvarchar(max)') AS NewValue
FROM @xml.nodes('/LogMessage/Fields/*') A(fld);

The result

FieldName           OldValue    NewValue
-----------------------------------------
TransactionCount    NULL        NULL
PersonnelType       Contractor  Employee 
Disabled            TRUE        FALSE 
Expiration          10/31/2018  12/31/2019

UPDATE

The same against a table's column:

DECLARE @mockup TABLE(ID INT IDENTITY,YourXml XML)
INSERT INTO @mockup VALUES
('<LogMessage>
   <Fields>
      <TransactionCount />
      <PersonnelType>
          <OldValue> Contractor </OldValue>
          <NewValue> Employee </NewValue>
      </PersonnelType>
      <Disabled>
          <OldValue> TRUE </OldValue>
          <NewValue> FALSE </NewValue>
      </Disabled>
      <Expiration>
          <OldValue> 10/31/2018</OldValue>
          <NewValue> 12/31/2019 </NewValue>
      </Expiration>
   </Fields>
</LogMessage>');

SELECT fld.value('local-name(.)','nvarchar(max)') AS FieldName
      ,fld.value('(OldValue/text())[1]','nvarchar(max)') AS OldValue
      ,fld.value('(NewValue/text())[1]','nvarchar(max)') AS NewValue
FROM @mockup m
OUTER APPLY m.YourXml.nodes('/LogMessage/Fields/*') A(fld)

What type is returned from nodes() in a SQL Server XML query

The official documentation says it is a rowset. Here is an excerpt from it:

The result of the nodes() method is a rowset that contains logical
copies of the original XML instances. In these logical copies, the
context node of every row instance is set to one of the nodes that is
identified with the query expression. This way, later queries can
navigate relative to these context nodes.

nodes() Method (xml Data Type)

Select query with SQL XML empty element

You can ISNULL it to an empty string

SELECT Data.value('(/Data/Reference)[1]', 'nvarchar(10)') as  PolicyNumber
       ,[RequestId]
       ,ISNULL([BatchEntryId], '') AS BatchEntryId
       ,[StatusCode]
       ,[PaymentMethodCode]
       ,Data.value('(/Data/Amount)[1]', 'nvarchar(10)') as  Amount
    FROM [dbo].[TransmissionData]
    WHERE RequestId = 2031070233
    FOR XML RAW ('RequestRecord'), ELEMENTS, TYPE

If BatchEntryId is not varchar or nvarchar you should cast it first

ISNULL(CAST(BatchEntryId AS varchar(30)), '') AS BatchEntryId

Note that SQL Server generates it as

<BatchEntryId></BatchEntryId>

however this is semantically equivalent in XML to

<BatchEntryId/>

SQL Query to convert XML Data to Columns in Table

Please try the following solution.

It is a minimal reproducible example. Just copy it as-is to SSMS and run it.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (page_id INT, entry_id INT, page_xml XML);
INSERT INTO @tbl (page_id, entry_id, page_xml) VALUES
(2219, 1996, N'<xsd:ArrayOfControl xmlns:xsd="http://tempuri.org/FormPersistance.xsd">
  <xsd:Control>
    <xsd:N>pg1_UIObject1</xsd:N>
    <xsd:V />
    <xsd:T>StlAnnotationControl</xsd:T>
    <xsd:A />
  </xsd:Control>
  <xsd:Control>
    <xsd:N>pg1_TabItem1Panel1</xsd:N>
    <xsd:V />
    <xsd:T>StlCanvas</xsd:T>
    <xsd:A />
  </xsd:Control>
  <xsd:Control>
    <xsd:N>pg1_txtPrefix</xsd:N>
    <xsd:V>MS</xsd:V>
    <xsd:T>StlTextBox</xsd:T>
    <xsd:A />
  </xsd:Control>
  <xsd:Control>
    <xsd:N>pg1_txtSurname</xsd:N>
    <xsd:V>Joey</xsd:V>
    <xsd:T>StlTextBox</xsd:T>
    <xsd:A />
  </xsd:Control>
  <xsd:Control>
    <xsd:N>pg1_txtNO</xsd:N>
    <xsd:V>400232</xsd:V>
    <xsd:T>StlTextBox</xsd:T>
    <xsd:A />
  </xsd:Control>
</xsd:ArrayOfControl>');

DECLARE @targetTbl TABLE (page_id INT, entry_id INT, N VARCHAR(30), V VARCHAR(30), T VARCHAR(30), A VARCHAR(30));
-- DDL and sample data population, end

WITH XMLNAMESPACES (DEFAULT 'http://tempuri.org/FormPersistance.xsd')
INSERT INTO @targetTbl (page_id, entry_id, N, V, T, A) 
SELECT page_id, entry_id, 
   c.value('(N/text())[1]', 'NVARCHAR(30)') AS N,
   c.value('(V/text())[1]', 'NVARCHAR(30)') AS V,
   c.value('(T/text())[1]', 'NVARCHAR(30)') AS T,
   c.value('(A/text())[1]', 'NVARCHAR(30)') AS A
FROM @tbl
    CROSS APPLY page_xml.nodes('/ArrayOfControl/Control') AS t(c);

-- test
SELECT * FROM @targetTbl;

Output

+---------+----------+--------------------+--------+----------------------+------+
| page_id | entry_id |         N          |   V    |          T           |  A   |
+---------+----------+--------------------+--------+----------------------+------+
|    2219 |     1996 | pg1_UIObject1      | NULL   | StlAnnotationControl | NULL |
|    2219 |     1996 | pg1_TabItem1Panel1 | NULL   | StlCanvas            | NULL |
|    2219 |     1996 | pg1_txtPrefix      | MS     | StlTextBox           | NULL |
|    2219 |     1996 | pg1_txtSurname     | Joey   | StlTextBox           | NULL |
|    2219 |     1996 | pg1_txtNO          | 400232 | StlTextBox           | NULL |
+---------+----------+--------------------+--------+----------------------+------+

Extrapolation data from XML with Query in SQL Server

There is no need to use OPENXML() and its companions sp_xml_preparedocument and `sp_xml_removedocument. They are kept just for backward compatibility with the obsolete SQL Server 2000.

Starting from SQL Server 2005 onwards, it supports XML data type methods and XQuery language. The example below is using one-to-many relationship simulation via CROSS APPLY clause and different aliases for different levels in the XML structure. The metadata fragment is shredded based on the @name attribute value.

Check it out.

SQL

-- directly from the XML file as a virtual DB table on the file system
;WITH XMLNAMESPACES (DEFAULT 'http://www.uni.com/U3011/sincro/', 'http://andxor.it/tDoc/metadata.xsd' AS m)
, rs (xmldata) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) AS BulkColumn 
   FROM OPENROWSET(BULK 'e:\Temp\Lotto_3.xml', SINGLE_BLOB) AS x
)
SELECT c.value('(Path/text())[1]', 'VARCHAR(30)') AS [Path]
   , x.value('(m:meta[@name="Data"]/@value)[1]','DATE') AS [Data1]
   , x.value('(m:meta[@name="Ragione Sociale"]/@value)[1]','VARCHAR(50)') AS [Name]
   , x.value('(m:meta[@name="Numero Protocollo"]/@value)[1]','VARCHAR(50)') AS [VatCode]
   , x.value('(m:meta[@name="Partita Iva"]/@value)[1]','VARCHAR(50)') AS [number]
   , tbl.xmldata.value('(/IdC/SelfDescription/ID/text())[1]','VARCHAR(50)') AS [ID]
FROM rs AS tbl
   CROSS APPLY tbl.xmldata.nodes('/IdC/FileGroup/File') AS t(c)
   CROSS APPLY t.c.nodes('MoreInfo/EmbeddedMetadata/m:metadata') AS meta(x);

Output

+-------------------+------------+---------------------+------------+---------------+---------------+
|       Path        |   Data1    |        Name         |  VatCode   |    number     |      ID       |
+-------------------+------------+---------------------+------------+---------------+---------------+
| docs/002D4DC0.pdf | 2019-04-30 | SCOTT ITALIA S.R.L. | 9102154398 | IT03770200164 | 1558224025077 |
| docs/002D4DC2.pdf | 2019-04-30 | SCOTT ITALIA S.R.L. | 9102154402 | IT03770200164 | 1558224025077 |
| docs/002D4DC4.pdf | 2019-04-30 | SCOTT ITALIA S.R.L. | 9102154401 | IT03770200164 | 1558224025077 |
| docs/002D4DC6.pdf | 2019-04-30 | SCOTT ITALIA S.R.L. | 9102154403 | IT03770200164 | 1558224025077 |
| docs/002D57A7.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164873 | IT03770200164 | 1558224025077 |
| docs/002D57A9.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164874 | IT03770200164 | 1558224025077 |
| docs/002D57AB.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164878 | IT03770200164 | 1558224025077 |
| docs/002D57AD.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164871 | IT03770200164 | 1558224025077 |
| docs/002D57AF.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164869 | IT03770200164 | 1558224025077 |
| docs/002D57B1.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164875 | IT03770200164 | 1558224025077 |
| docs/002D57B3.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164882 | IT03770200164 | 1558224025077 |
| docs/002D57B5.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164886 | IT03770200164 | 1558224025077 |
| docs/002D57B7.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164884 | IT03770200164 | 1558224025077 |
| docs/002D57B9.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164881 | IT03770200164 | 1558224025077 |
| docs/002D57BB.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164883 | IT03770200164 | 1558224025077 |
| docs/002D57BD.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164880 | IT03770200164 | 1558224025077 |
| docs/002D57BF.pdf | 2019-05-13 | SCOTT ITALIA S.R.L. | 9102164889 | IT03770200164 | 1558224025077 |
| docs/002D57C1.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164887 | IT03770200164 | 1558224025077 |
| docs/002D57C3.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164888 | IT03770200164 | 1558224025077 |
| docs/002D57C5.pdf | 2019-05-10 | SCOTT ITALIA S.R.L. | 9102164885 | IT03770200164 | 1558224025077 |
| docs/002D57C7.pdf | 2019-05-13 | SCOTT ITALIA S.R.L. | 9102164891 | IT03770200164 | 1558224025077 |
| docs/002D57C9.pdf | 2019-05-13 | SCOTT ITALIA S.R.L. | 9102164890 | IT03770200164 | 1558224025077 |
+-------------------+------------+---------------------+------------+---------------+---------------+

Related Topics

Create a Unique Index on a Non-Unique Column

Combine Output Inserted.Id with Value from Selected Row

Execute SQL File in Perl

MySQL Datetime Group by 15 Mins

Postgres Won't Accept Table Alias Before Column Name

SQL Multiple Like Statements

Creating Nondeterministic Functions in SQL Server Using Rand()

SQL Server - Invalid Characters in Parameter Names

Multiple Not Distinct

How to Get the End of a Day

How to Find Which Columns Don't Have Any Data (All Values Are Null)

How to Remove Part of the String in Oracle

Test for Upper Case - T-Sql

Keep Order from 'In' Clause

Redshift Split Single Dynamic Column into Multiple Rows in New Table

How to Get the Nth Row in a SQL Server Table

Sparksql Error Table Not Found

Listagg Alternative in Db2


Leave a reply



Submit