Forum Replies Created

Viewing 15 posts - 1 through 15 (of 1,409 total)

  • Reply To: Extract data from XML field

    Not totally clear to me what you're asking for, maybe this?

    with cte as (
    select REF_NO,
    n.x.value('Country[1]','varchar(30)') as Country,
    ...
  • Reply To: Extract data from XML field

    This should work pre 2016

    select stuff((select ',' + n.x.value('Country[1]','varchar(30)') as "text()"
    from MyView t
    cross apply t.GlobalCountryRegionXML.nodes('/GlobalCountryRegion/CountryRegion') n(x)
    order by row_number() over(order by n.x)
    for xml path('')),1,1,'') as Country,
    stuff((select ','...
  • Reply To: Extract data from XML field

    with cte as (
    select n.x.value('Country[1]','varchar(30)') as Country,
    n.x.value('Region[1]','varchar(30)') as Region,
    row_number() over(order by n.x) as rn
    from MyView t
    cross apply...
  • Reply To: Ranking with repeating groups

    with cte as (
    select TestType, TestDate, case when lag(TestType) over(order by TestDate) = TestType then 0 else 1 end as IsStart
    from #T
    )
    select TestType, TestDate,sum(IsStart) over(order by TestDate)...
  • Reply To: XML data extract

    Try this

    declare @MyTable table(Personcode int, XMLBody XML);

    insert into @MyTable(Personcode, XMLBody)
    values(99999,'<content>
    <fieldgroup>
    <field id="Comment" type="textbox" title="Comment:" rows="6" cols="50" cssclass="textbox" mandatory="1">Extract the data string located here.</field>
    <field id="Action" type="textbox" title="Action:" rows="6" cols="50"...
  • Reply To: Query with a column containing JSON text

    select t.Id, t.FileName, t.FolderName,
    kp.LicenceNo, kp.IDNumber, kp.Class, kp.CardNumber, kp.ExpiryDate
    from MyFiles t
    outer apply openjson(t.KeyPairs)
    with (LicenceNo int '$."Licence No "',
    ...
  • Reply To: grouping rows into sets

    WITH CTE1 AS (
    SELECT *,
    CASE WHEN LAG(t_type) OVER(PARTITION BY t_id ORDER BY t_date) = t_type THEN 0 ELSE 1 END AS...
  • Reply To: SQL JSON Ouptput

    declare @t table(codetype varchar(10), code int, Value int);
    insert into @t(codetype, code, Value)
    values('Test1',1,2),
    ('Test1',2,3),
    ('Test2',4,5),
    ('Test2',6,7);


    SELECT
    N'{' +
    STUFF(
    (
    ...
  • Reply To: Sum field with where clause based on a RecordId

    WITH Ranges AS (
    SELECT (RecordId - NumDays + 1) As Start, RecordId
    FROM #test
    WHERE NumDays > 0)
    UPDATE #test SET AmountAll = B.AmountAll
    FROM #test A
    JOIN (SELECT r.RecordId, SUM(t.Amount)...
  • Reply To: Extracting out elements from XML String based on condition in tag

    This should work for you

    select x.r.value('Index[1]','int') as ChannelID,
    x.r.value('(SensorScaling/OutUnits)[1]','varchar(20)') as [SI Units]
    from @DataSheetXML.nodes('Datasheet/ChannelList/ChannelDefinition[@IsEnableable="True"]') x(r);
  • Reply To: Extract out Parent/child records from XML String

    See if this helps

    select pd.a.value('let $a := . return 1 + count(../ProbeDefinition[. << $a])', 'int') AS MasterRecordNumber,
    cl.b.value('.','int') as ChannelListValue
    from @DataSheetXML.nodes('Datasheet/ProbeList/ProbeDefinition')...
  • Reply To: How to mark consequitive groups with non-unique group key

    with cte as (
    select ID, TS, FLAG,
    case when lag(flag) over(partition by id order by ID,TS) = flag then 0 else...
  • Reply To: Cross-Tab & Concatenate Rows with a join : Help with a Query

    SELECT m.displayid, STRING_AGG(m.promo_id,';') AS Concat_Promo_Id, STRING_AGG(h.promogroup,';') AS Concat_promogroup
    FROM Product_Main m
    LEFT OUTER JOIN Product_Helper h ON h.displayid = m.displayid AND h.promo_id = m.promo_id
    GROUP BY m.displayid;
  • Reply To: XML Parsing Problem

    Replace

    Data.Col.value('(./PartyID)[1]', 'int') As Party_ID

    with

    Data.Col.value('(../PartyID)[1]', 'int') As Party_ID

  • Reply To: How to select PartFamilyId and FamilyStatus is active or (active and null) based

    select t1.PartFamilyId, t1.FamilyStatus + isnull('|' + ca.FamilyStatus,'') as FamilyStatus
    from #partsFamily t1
    outer apply (select 'NULL' from #partsFamily t2 where t2.PartFamilyId = t1.PartFamilyId and t2.FamilyStatus is null) ca(FamilyStatus)
    where t1.FamilyStatus...

Viewing 15 posts - 1 through 15 (of 1,409 total)